Non-Duplicates

[Gary B]

Consider the follow table data. I need a query that will return the values
10002 & 10005 because they are the only rows that contain field TYPE with
non-similar values.

ID TYPE
10001 B
10001 B
10002 A
10002 A
10002 B
10003 A
10003 A
10004 B
10004 B
10004 B
10005 B
10005 A

Thank you!

 

[John Spencer (MVP)]

Two-query solution

Save the following as QueryA:
SELECT Distinct ID, [Type]
FROM YourTable

Use the saved query as the source for this query
SELECT ID
FROM QueryA
GROUP BY ID
HAVING Count([Type]) > 1

You can probably do all this in one query.

SELECT ID
FROM (SELECT Distinct ID, [Type]
FROM YourTable) as A
GROUP BY ID
HAVING Count([Type]) > 1

 

[PC Datasheet]

1002 has duplicates!

 

[Douglas J. Steele]

I don't think he cares about the duplicates, but the fact that there's more
than one distinct value associated with 10002.

The following SQL will work in Access 2000 and newer:

SELECT ID FROM
(SELECT DISTINCT ID, TYPE FROM GarysTable) AS DistinctValues
GROUP BY ID
HAVING COUNT(TYPE) > 1

In Access 97 and earlier, you'll need to create a query that returns only
the distinct values and save it as qryDistinctValues:

SELECT DISTINCT ID, TYPE
FROM GarysTable

You'd then create a 2nd query that uses qryDistinctValues:

SELECT ID
FROM qryDistinctValues
GROUP BY ID
HAVING COUNT(TYPE) > 1