Find Duplicates Query - Excluding Items

[Wahnsinn]

Hi guys, I'm working on some queries to help identify duplicates within a set of data we've got (invoices) and this query is great. The only issue I'm coming across is when I use the query for different types of checks, I don't have a way within the query to exclude duplicate checks.

For example, my first query looks for duplicates where the invoice number, invoice date, invoice amount, and vendor ID exactly match. The second query is where the vendor ID, invoice date, and invoice amount exactly match, but the invoice number is just similar.

Problem is, all the items that show on the first query are also going to show on the second query, along with the new items.

I attempted to add some text to the criteria ([TranNo]<>[TestData].[TranNo]), but that killed the query and returned no results.

I'm also not able to just export and run a vlookup to exclude all items from the first query, as one of those duplicates could match with a similar invoice from the second query (leaving the similar item by itself).

I've been searching the last few days but have come up empty-handed thus far.

Any thoughts?



Thanks in advance!

Tim

[Wahnsinn]

FYI, here's the criteria for the second query (used the wizard):

In (SELECT [VendID] FROM [TestData] As Tmp GROUP BY [VendID],[ExtractTran],[TranDate],[TranAmtHC] HAVING Count(*)>1 And [ExtractTran] = [TestData].[ExtractTran] And [TranDate] = [TestData].[TranDate] And [TranAmtHC] = [TestData].[TranAmtHC])