SELECT UNIQUE

S

Steve

I find myself in a situation where I really need the UNIQUE statement(it
looks like this is not available in Access)

My situation is the following

<tables>
[Tbl_BaseProtocols]
BaseProtocolID PK
DeviceID FK
ProtocolID FK


[Tbl_Protocols]
ProtocolID PK
Name


[Tbl_ProtocolSegments]
SegmentID PK
ProtocolID FK
</tables>

<sample data>
[Tbl_BaseProtocols]
[DeviceID] [ProtocolID]
1 2
1 3
1 4
2 2
2 3
2 4
3 2
3 3
3 4

* although devices currently share the same protocols, this could change in
the future so I have planned for it now

[Tbl_Protocols]
[ProtocolID] [Name]
2 "Test"
3 "Test"
4 "Test"

[Tbl_ProtocolSegments]
[SegmentID] [ProtocolID]
1 2
2 2
3 2
4 3
5 3
6 4
</sample data>


I am attempting to use a DataSet and DataRelations (ADO.net stuff) to relate
this data in memory on the client. To do that, I have two Access queries;
*usp_GetBaseProtocols*
SELECT Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_Protocols.Name
FROM Tbl_Protocols INNER JOIN Tbl_BaseProtocols ON Tbl_Protocols.ProtocolID
= Tbl_BaseProtocols.ProtocolID
ORDER BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolNum;

*usp_GetBaseProtocolSegments*
SELECT Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_ProtocolSegments.SegmentID
FROM (Tbl_Protocols INNER JOIN Tbl_BaseProtocols ON Tbl_Protocols.ProtocolID
= Tbl_BaseProtocols.ProtocolID) INNER JOIN Tbl_ProtocolSegments ON
Tbl_Protocols.ProtocolID = Tbl_ProtocolSegments.ProtocolID
ORDER BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolNum,
[Tbl_ProtocolSegments.ProtSegID];


Now, finally... the problem. The second query returns duplicate records.
Because I have designed the DB so that Devices all share the same
protocols(until later we decide to change this), when I try to get just the
segments that correlate to the base protocols, I am getting them in
triplicate.

I don't think I mentioned that other than the "Base protocols" and their
respective segments, there are "Custom Protocols" and THEIR respective
segments. So I'm using Tbl_BaseProtocols to get only the segments that
relate to the base Protocols.

I hope this makes sense.

Here is sample data from my queries:
<sample query results>
*usp_GetBaseProtocols*
DeviceID ProtocolID Name
1 2 ABR. 2/2
1 3 ABR. 6/6
1 4 ABR. 9/9
1 5 RAMP LOW

*usp_GetBaseProtocolSegments*
DeviceID ProtocolID ProtSegID
1 2 1
1 2 2
2 2 1
2 2 2
3 2 1
3 2 2
</sample query results>

Hopefully that makes sense and you can help me ;)

Thanks for reading this far,
Steve
 
K

Ken Snell [MVP]

Try one of these for the second query (note: this will make the query
nonupdatable):

*usp_GetBaseProtocolSegments*
SELECT DISTINCT Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_ProtocolSegments.SegmentID
FROM (Tbl_Protocols INNER JOIN Tbl_BaseProtocols ON Tbl_Protocols.ProtocolID
= Tbl_BaseProtocols.ProtocolID) INNER JOIN Tbl_ProtocolSegments ON
Tbl_Protocols.ProtocolID = Tbl_ProtocolSegments.ProtocolID
ORDER BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolNum,
[Tbl_ProtocolSegments.ProtSegID];


or


*usp_GetBaseProtocolSegments*
SELECT Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_ProtocolSegments.SegmentID
FROM (Tbl_Protocols INNER JOIN Tbl_BaseProtocols ON Tbl_Protocols.ProtocolID
= Tbl_BaseProtocols.ProtocolID) INNER JOIN Tbl_ProtocolSegments ON
Tbl_Protocols.ProtocolID = Tbl_ProtocolSegments.ProtocolID
GROUP BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_ProtocolSegments.SegmentID
ORDER BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolNum,
[Tbl_ProtocolSegments.ProtSegID];

--

Ken Snell
<MS ACCESS MVP>

Steve said:
I find myself in a situation where I really need the UNIQUE statement(it
looks like this is not available in Access)

My situation is the following

<tables>
[Tbl_BaseProtocols]
BaseProtocolID PK
DeviceID FK
ProtocolID FK


[Tbl_Protocols]
ProtocolID PK
Name


[Tbl_ProtocolSegments]
SegmentID PK
ProtocolID FK
</tables>

<sample data>
[Tbl_BaseProtocols]
[DeviceID] [ProtocolID]
1 2
1 3
1 4
2 2
2 3
2 4
3 2
3 3
3 4

* although devices currently share the same protocols, this could change
in
the future so I have planned for it now

[Tbl_Protocols]
[ProtocolID] [Name]
2 "Test"
3 "Test"
4 "Test"

[Tbl_ProtocolSegments]
[SegmentID] [ProtocolID]
1 2
2 2
3 2
4 3
5 3
6 4
</sample data>


I am attempting to use a DataSet and DataRelations (ADO.net stuff) to
relate
this data in memory on the client. To do that, I have two Access queries;
*usp_GetBaseProtocols*
SELECT Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_Protocols.Name
FROM Tbl_Protocols INNER JOIN Tbl_BaseProtocols ON
Tbl_Protocols.ProtocolID
= Tbl_BaseProtocols.ProtocolID
ORDER BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolNum;

*usp_GetBaseProtocolSegments*
SELECT Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_ProtocolSegments.SegmentID
FROM (Tbl_Protocols INNER JOIN Tbl_BaseProtocols ON
Tbl_Protocols.ProtocolID
= Tbl_BaseProtocols.ProtocolID) INNER JOIN Tbl_ProtocolSegments ON
Tbl_Protocols.ProtocolID = Tbl_ProtocolSegments.ProtocolID
ORDER BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolNum,
[Tbl_ProtocolSegments.ProtSegID];


Now, finally... the problem. The second query returns duplicate records.
Because I have designed the DB so that Devices all share the same
protocols(until later we decide to change this), when I try to get just
the
segments that correlate to the base protocols, I am getting them in
triplicate.

I don't think I mentioned that other than the "Base protocols" and their
respective segments, there are "Custom Protocols" and THEIR respective
segments. So I'm using Tbl_BaseProtocols to get only the segments that
relate to the base Protocols.

I hope this makes sense.

Here is sample data from my queries:
<sample query results>
*usp_GetBaseProtocols*
DeviceID ProtocolID Name
1 2 ABR. 2/2
1 3 ABR. 6/6
1 4 ABR. 9/9
1 5 RAMP LOW

*usp_GetBaseProtocolSegments*
DeviceID ProtocolID ProtSegID
1 2 1
1 2 2
2 2 1
2 2 2
3 2 1
3 2 2
</sample query results>

Hopefully that makes sense and you can help me ;)

Thanks for reading this far,
Steve
Click to expand...
 
S

Steve

Hi,

Cool, DISTINCT is what I was looking for. Funny I wrote that LONG post when
all I needed to say was"Is there a statement like UNIQUE in Access?"

DOH!

Thanks for the solution, take it easy,
Steve



Ken Snell said:
Try one of these for the second query (note: this will make the query
nonupdatable):

*usp_GetBaseProtocolSegments*
SELECT DISTINCT Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_ProtocolSegments.SegmentID
FROM (Tbl_Protocols INNER JOIN Tbl_BaseProtocols ON Tbl_Protocols.ProtocolID
= Tbl_BaseProtocols.ProtocolID) INNER JOIN Tbl_ProtocolSegments ON
Tbl_Protocols.ProtocolID = Tbl_ProtocolSegments.ProtocolID
ORDER BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolNum,
[Tbl_ProtocolSegments.ProtSegID];


or


*usp_GetBaseProtocolSegments*
SELECT Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_ProtocolSegments.SegmentID
FROM (Tbl_Protocols INNER JOIN Tbl_BaseProtocols ON Tbl_Protocols.ProtocolID
= Tbl_BaseProtocols.ProtocolID) INNER JOIN Tbl_ProtocolSegments ON
Tbl_Protocols.ProtocolID = Tbl_ProtocolSegments.ProtocolID
GROUP BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_ProtocolSegments.SegmentID
ORDER BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolNum,
[Tbl_ProtocolSegments.ProtSegID];

--

Ken Snell
<MS ACCESS MVP>

Steve said:
I find myself in a situation where I really need the UNIQUE statement(it
looks like this is not available in Access)

My situation is the following

<tables>
[Tbl_BaseProtocols]
BaseProtocolID PK
DeviceID FK
ProtocolID FK


[Tbl_Protocols]
ProtocolID PK
Name


[Tbl_ProtocolSegments]
SegmentID PK
ProtocolID FK
</tables>

<sample data>
[Tbl_BaseProtocols]
[DeviceID] [ProtocolID]
1 2
1 3
1 4
2 2
2 3
2 4
3 2
3 3
3 4

* although devices currently share the same protocols, this could change
in
the future so I have planned for it now

[Tbl_Protocols]
[ProtocolID] [Name]
2 "Test"
3 "Test"
4 "Test"

[Tbl_ProtocolSegments]
[SegmentID] [ProtocolID]
1 2
2 2
3 2
4 3
5 3
6 4
</sample data>


I am attempting to use a DataSet and DataRelations (ADO.net stuff) to
relate
this data in memory on the client. To do that, I have two Access queries;
*usp_GetBaseProtocols*
SELECT Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_Protocols.Name
FROM Tbl_Protocols INNER JOIN Tbl_BaseProtocols ON
Tbl_Protocols.ProtocolID
= Tbl_BaseProtocols.ProtocolID
ORDER BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolNum;

*usp_GetBaseProtocolSegments*
SELECT Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolID,
Tbl_ProtocolSegments.SegmentID
FROM (Tbl_Protocols INNER JOIN Tbl_BaseProtocols ON
Tbl_Protocols.ProtocolID
= Tbl_BaseProtocols.ProtocolID) INNER JOIN Tbl_ProtocolSegments ON
Tbl_Protocols.ProtocolID = Tbl_ProtocolSegments.ProtocolID
ORDER BY Tbl_BaseProtocols.DeviceID, Tbl_Protocols.ProtocolNum,
[Tbl_ProtocolSegments.ProtSegID];


Now, finally... the problem. The second query returns duplicate records.
Because I have designed the DB so that Devices all share the same
protocols(until later we decide to change this), when I try to get just
the
segments that correlate to the base protocols, I am getting them in
triplicate.

I don't think I mentioned that other than the "Base protocols" and their
respective segments, there are "Custom Protocols" and THEIR respective
segments. So I'm using Tbl_BaseProtocols to get only the segments that
relate to the base Protocols.

I hope this makes sense.

Here is sample data from my queries:
<sample query results>
*usp_GetBaseProtocols*
DeviceID ProtocolID Name
1 2 ABR. 2/2
1 3 ABR. 6/6
1 4 ABR. 9/9
1 5 RAMP LOW

*usp_GetBaseProtocolSegments*
DeviceID ProtocolID ProtSegID
1 2 1
1 2 2
2 2 1
2 2 2
3 2 1
3 2 2
</sample query results>

Hopefully that makes sense and you can help me ;)

Thanks for reading this far,
Steve
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem designing a form envolving many-to-many relationships 3
Append query 6
Unique Count in Query 17
UNION query 11
Pattern design to be easy queried. 0
Implementing a many-to-many relationship 4
Neep help For The Query 3
Query - At my wits end! 1

Top