Complex Find Duplicate Query

[Neilbees]

Hi folks

Apologies for a new thread but I've got a real head-scratcher that I hope someone can help with. Couldn't find anything to solve this using the search.

It's basically trying to find duplicates in a list of invoices, but with an added complication. We are interested in 3 fields - Invoice Number, Vendor Name and Reference Number.

If we were looking at 2 separate records -

- If all 3 fields in the 2 records are identical then this is NOT a duplicate.
- If Invoice Number and Vendor Name in the 2 records are identical but Reference Number is different in each then it IS a duplicate. They are the ones we need to find.

Hope that makes sense and that someone can point me in the direction of how to solve this in a query.

Thanks!

 

[Thomko]

I would propopse a solution with helper queries.

First create a query Q1 that lists the records where all three fields are identical (NOT a duplicate per your definition)
Now create a query Q2 that lists all records with identical Invoice Number and Vendor Name (should contain all records from Q1 and the real duplicates)

Now the trick: Use the first query as a filter for the second.
Simplest approach: Build a third query Q3 where you join Q1 and Q2 on the record ID with an outer(!) join and add an "Is Null" condition to the record ID on the Q1 side. This will eliminate all records listed in Q1 from the resulting record set, leaving only the REAL duplicates as the output of Q3.

HTH
Thomas

 

[Neilbees]

Great stuff Thomas, you're a genius. Many thanks!