Finding 'true' duplicate rows in access table

[rmuzam]

Hi, I need help in finding 'true' duplicates. I am trying to find duplicate payments in 'Payments' table.
Sample 'VednorNo' and 'Amount' field entries:

A 100
B 50
A -100
A 100
C 40
B 50


Running simple duplicate query from access will report duplicates on amount 100 and 50. But 100 and -100 cancel each other for Vendor A, therefore 100 is not a duplicate payment. However, amount 50 for Vendor B is a correct duplicate payment. Can you please help me write a query to extract the results as mentioned?

Thanks a lot for your help.

 

[PWise]

SELECT payment.cus, IIf([amt]>0,[amt],-[amt]) AS Expr1
FROM payment
GROUP BY payment.cus, IIf([amt]>0,[amt],-[amt])
HAVING (((Sum(payment.amt))=(IIf([amt]>0,[amt],-[amt])*Count(IIf([amt]>0,[amt],-[amt])))) 
AND ((Count(IIf([amt]>0,[amt],-[amt])))>1));

 

[JoeAtWork]

I don't know how your tables are set up, but I think a better way to find true duplicates is having more fields in your Payments table that you can match up. For example, an InvoiceNo or PurchaseOrderNo.

That way, instead of looking for amounts that happen to be the same, you can be assured that the payments really were for the same thing.

 

[rmuzam]

pwise: Thanks for your quick response. However, I am not getting the desired result. I am getting a lot of entries with Expr1 = -0. Could you please elaborate on what should I do to sort this out.

JoeAtWork: Thanks - I alrady have more fields in the table but I didn't want to overload you guys with information and that's why I simplified my request.

 

[chinnashara]

Can some one help me in figuring out the duplicate entries based on the below requirements.

I have a table with have a field for machine name
I have a table for the serial number for these machine names
I have a table for the date registered field for these machines.

As we can have many machines with the duplicate serial numbers, we need to figure out those machines with the duplicate serial numbers and having the oldest date registered.

EX: Machine Name Serial Number Date registered

Machine1 Ma1SNO1 31-11-07
Machine2 Ma1SNO1 20-11-07
Machine3 MA3SNO3 13-12-07
Machine5 MA4SNO4 14-9-07

Now, even Machine1 and Machine2 have the same serial number I need only the machine2 as it contains the oldest Date Registered


Please help me

Thanks in Advance

 

[jrbarnett]

Chinnashara

Please start a new thread for your question, as it doesn't relate to the rest of this question.

John

 

[fneily]

What JoeAtWork is saying is that you shouldn't have any duplicate payments if your table is set up correctly. Thus some key like InvoiceNo. Withholding your field names doesn't simplify the question. It adds confusion to the readers. We need to see your table structure to give correct advice. Right now, to me, you have an invalid Access table.