Finding duplicates in two fields

[skipnick]

I have a table with something like 30000 entries, and I would like it to show me only the entries that have different corresponding field 2 values for their field 1 value. For instance, if my first entry had a field 1 value of 1 and a field 2 value of A, I would like my query to neglect to show me all other entries that have 1 and A and then show me any entries that have a 1, but a different letter to go with it. It's hard to explain and I'm sure there must be an easy way to solve it.



In otherwords i would like it to show me all combinations of field 1 and field 2 that appears in the table, but only once

[June7]

Try GROUP BY or DISTINCT or DISTINCT ROW.

The first is an aggregate (Totals) query.

SELECT field1, field2 FROM table GROUP BY field1, field2;

The other two are properties of a query that can be set in the query builder Property Sheet.

[skipnick]

the GROUP BY query worked a treat, thanks

[skipnick]

How could i go about having it show my other fields at the same time? If i select all fields in the GROUP BY query, it tries to find duplicates of all fields

[June7]

If you have several records with 1 and A, how will the query know which record to show? Which one do you want to be shown? Is there a unique ID field (such as autonumber)?

[skipnick]

It doesn't matter which is shown, each field 1 has only one correct field 2 value, how ever some if my untried have false data so I only need one of each combination be they correct or incorrect. The incorrect ones however, I need the rest of my fields so I can find the location of the incorrect data and change it

[June7]

Not sure I understand that.

Maybe use TOP N to return one record for each field1/field2 pair. Requires nested subquery and probably a unique ID field (autonumber will serve). Review http://allenbrowne.com/subquery-01.html#TopN