Finding conflicting data

[Dale Peart]

Can someone tell me how to find lines with conflicting data from a table w/o
a Primary Key?

Example:

ID Location
22546 12
22546 53

Obviously this item can't be in both places (items are uniquely numbered).

Dale

 

[John Spencer (MVP)]

Looks like a find duplicates query to me. Have you tried building one with the
wizard? The SQL statement would look something like

SELECT ID, Location
FROM YourTableName
WHERE ID IN
(SELECT T.ID FROM YourTableName As T GROUP BY T.ID HAVING COUNT(*)>1)

If you were doing this in the query grid. The criteria would be something like

Field: ID
Table: YourTableName
Criteria: IN (SELECT T.ID FROM YourTableName As T GROUP BY T.ID HAVING COUNT(*)>1)

If your table is allowed to have the same ID and Location multiple times but not
any other combination, then this is a bit more complex. In other words, the
following set of records is legal.

ID Location
22546 12
22546 12
22456 12

The easiest way would be to build a query that gives you all the distinct
combinations and then use that as the source for a query to give you duplicates
(see query above).