Exclude records found in Find Duplicates Query

[VorLag]

I used the wizard to build a Find Duplicates query and want to now build another query to list records that exclude those in that Find Duplicates query. How can I do this?

 

[Joe4]

Create an Unmatched Query from your original data set to your Duplicate Query. There is a Wizard which you can use for this, assuming that you are joining these two by just one field (if more, first do it for just one field, then open the Query in Design mode and add your other Left Outer Joins).

 

[VorLag]

That works for getting rid of all of the records, but I realized that it gets rid of the first instance of each record for which there is a duplicate as well. Is there a way to keep the first instance of each record and exclude the second, third, etc?

 

[Joe4]

You should be able to use an Aggregate Query to get what you want.

Create a query and select all the records you want to return.
In Query Builder, click on the "Totals" button (looks like a Sigma). This will add a Totals row under each field with the option "Group By" selected for each one.
View the results.

This will collapse all duplicates and only show each record once.

 

[VorLag]

Thanks, that works!

 

[Joe4]

You're welcome.
Aggregate Queries are a good tool that I use often. They are also great for grouping records and finding counts, sums, etc.