I have a table(Invoice) of paid invoices to other companies. I want to ensure that no duplicate payments have been made in 300,000 plus invoices so I run separate queries using different criteria.
1) VendNo, InvNo, InvAmtAbs, InvDate (qryDuplicates0)
2) InvNo, InvAmtAbs, InvDate (qryDuplicates1)
3) InvAmtAbs, InvDate (qryDuplicates2)
4) InvNo, InvAmtAbs (qryDuplicates3)
Since qryDuplicates0 has the most criteria, it produces the fewest answers. However those same answers appear again in qryDuplicates1, qryDuplicates2 and qryDuplicates3. And so on.


I want to create a DuplicatesXref table where each of these queries places the ID record of the results of each query, and in another field notes which query it came from (i.e. 0, 1, 2,3). So an entry of a record that ends up from all 4 queries would look like:
ID Pull
### 0123
I would then create a query on DuplicateXref joined to Invoice to show me all records (with additional fields) in one table(once per ID). After that, probably a macro to run them in sequence.
Data cannot be deleted because it is the record of payments made. I just want to identify through manual review if someone was over paid.
Any help on how to set up the queries and DuplicateXref table would be greatly appreciated!