Find duplicates with additional condition

[xyz321]

Hi

I am trying to find duplicates in a table of data but there is an additional condition to find the duplicates. I will try to explain what i am trying to do.

The table has two columns. one is Company invoice no and second is client invoice no. Company invoice no is our company reference no while client invoice no is the client's reference no.

All invoices are recorded twice in the database. First entry is when they go out to client and second entry is when they come back approved from client. For both entries company invoice no remains the same while the client invoice will be different in the sense that the word "Approved" (word is always same) is added to the client invoice no.

I am trying to find duplicate company invoice no but when i do this through a query in access it would come up with all records as twice because one entry is for invoice going out and second is for invoice coming in.

I only need to find duplicate invoice company no where client invoice no is the same.

I am attaching an example excel sheet. Ideally the query should find duplicate no as "123" as it appears both against ABC1 and ABC3.

Please help me. i tried using Instr function to filter records with Approved at the end but could not get it to work.

 

[James Dudden]

I'm not sure if this will help but if you add a field to your query as follows:

temp: IIf(InStr([Client Invoice no],"Approved"),1,0)

Then in the criteria put 0 - this will eliminate all the records with "Approved" in them.

 

[xyz321]

I'm not sure if this will help but if you add a field to your query as follows:

temp: IIf(InStr([Client Invoice no],"Approved"),1,0)

Then in the criteria put 0 - this will eliminate all the records with "Approved" in them.

Thanks for ur suggestion. That can work but the problem is that if there are duplicate numbers assigned to two "approved" invoices then it will not pick up as the "approved" records will be eliminated from the results.

 

[jal]

Make sure your table has an autonumber column. A standard query for eliminating dups is this:

DELETE FROM Customers WHERE AutoNum NOT IN
(
SELECT Min(AutoNum) FROM Customers
GROUP BY ClientInvoiceNo, CompanyInvoiceNo
)

To test this, you can run the SELECT part by itself which will return a list of those records that will be retained (the rest will be deleted).

 

[jal]

Wait a minute.... maybe I got that wrong.....

 

[jal]

did you solve this? I ran out of time, had to go to work. Maybe I'll take another look at it.

 

[jal]

Ok here's the new version:


DELETE FROM Customers WHERE AutoNum NOT IN
(
SELECT Min(AutoNum)
FROM Customers
GROUP BY CompanyInvoiceNo,(Instr(ClientInvoiceNo, 'Approved') > 0)
)

And for testing, run this SELECT to see which rows will be retained (the rest will be deleted)


SELECT Min(AutoNum) as TheAutoNum, CompanyInvoiceNo, (Instr(ClientInvoiceNo, 'Approved') > 0) as Approved
FROM Customers
GROUP BY CompanyInvoiceNo, (Instr(ClientInvoiceNo, 'Approved') > 0)
ORDER BY Min(AutoNum)