Find Duplicates

[ypma]

Good morning programmers, I am happy with using the query wizard to find duplicate records which informs me the number of record that are duplicated, is this case the field was Phone numbers. What I am trying to achieve is a result that shows each record that is duplicated.

Home Number Field Import No Id number of duplicates
1234567 564 2

I would like the query result showing both records

1234567 564
1234567 593
I could create a query with criteria of the duplicate phone number; however there are numerous records to processes. For this exercise the number count of duplicates is not required.
Any advice would be much appreciated

Regards Ypma

 

[CJ_London]

bit confused by your example which is showing duplicates against an ID.

So suspect you need another field - such as the ID for the record (your example ID is not it since it reports duplicates), then in the query wizard, select that ID field as an additional field (next step from selecting the duplicate fields)

or perhaps your example is wrong, so select phone number as the duplicate field and the ID as an additional field

 

[ypma]

CJ, Thank you for your response, I only showed a couple of fields in my post , these records are imported and the Import number is the source providers ID , The id for each record is shown on my working query , sorry about that . I am only trying to fine duplicates of phone numbers to enable a refund for these duplicate leads to be claimed for.

in a nutshell can I list all duplicate records and not involve a count ? and not the other records within the record set ?

Regards Ypma

#Home Number Field Import No Id clientID

12345 567 M1245
12345 569 M1248#

 

[CJ_London]

need to be clearer - you need a uniqueID for each record, you haven't said whether you have or you havent

If necessary import data to a temporary table and add an autonumber field

 

[Mark_]

One way to do this is make ONE query that tells you which phone numbers are duplicated and omits non-duplicate records.

Make a second query that uses the first as its data source. Join it to the table you want to pull clientID from. You should get one record from the table for each ID that has a match in the phone number list.

Does this help?