find duplicates query

A

ashaback

I have a table that has two columns that are unique fields, item code and
city. I want to run a duplicate query for any duplicates of item and city.
When I go to create this query it only give me one option to choose the
duplicate fields.

How can I create this query by telling it to look at item and city then find
the duplicates?

Thanks a bunch.
 
J

John Vinson

I have a table that has two columns that are unique fields, item code and
city. I want to run a duplicate query for any duplicates of item and city.
When I go to create this query it only give me one option to choose the
duplicate fields.

How can I create this query by telling it to look at item and city then find
the duplicates?

Thanks a bunch.
Click to expand...

You should be able to use the duplicate wizard, but it's easy enough
to "roll your own".

Create a Query with three fields: the primary key of your table (or
actually any field which you can count on to be non-NULL for all
records), the Item, and the City.

Make it a Totals query by clicking the Greek Sigma icon.

Leave the default "Group By" on the totals row for Item and City, and
change it to Count on the other field.

Put a criterion of
1
Click to expand...

on the count.

This will show you all pairings of item and city which have
duplicates.

Note that you can prevent duplicates from happening in the first place
by creating an index on the two fields and specifying that it is a
unique index.

John W. Vinson[MVP]
 
Top