Richard
In MS Access, I have a query that shows any duplicate postcodes I have in a table. (the query is Find Duplicates for Billing Customers)
I now want to query through those results, and pick out any which have the same house number at the same postcode (ie a customer has placed 2 orders at the same address)
Please suggest\direct any code you think I need. I've looked along the lines of IFF(left(AddressFirstLine,2)==(
not sure how to get it to check the other results....
Thanks
Kev

---

Answer from Richard Rost:

How about an aggregate query? You could GROUP BY post code and street address, and then perform a COUNT of how many instances of that exist. If you see 2 or more, you know you have some duplicates.

To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little button on the query toolbar that looks like a wierd "E" (actually the Greek letter Sigma). Press that and you get a new Total row. Just add the fields you want to group by (like customer name or ID) and the fields you want to run calculations on (like SUM, Count or Average) and there you go.

I have a FREE tutorial on my web site that teaches you how to create an Aggregate Query. See:

http://599cd.com/tips/access/aggregate-query?key=BlFAlEx

Enjoy!