Query for duplicate, then perform calculation.

[Lisa]

Is there a function that will find duplicate records and
then if it is duplicate it will then perform a calculation?
And where would I put this function?

Example:
If Invoice ## is listed more than once, divide by # of
times listed.

Thanks

Lisa

 

[Lisa]

I am looking for the invoice amount divided by the # of
times it's duplicated. Example: Invoice = $150 and is
listed 2 times. I want a calculation that = $75. OR
Invoice = $150, listed 3 times = $50.

I have never used access before yesterday, so I am a
novice. I wrote several other queries for this report
based on date range and another based on an If statement
and they work just lovely. Any suggestions would be
appreciated.

Thanks

-----Original Message-----
If you're looking for the invoice amounts, you can either

use SELECT DISTINCT (if you're comfortable writing your
own SQL) or use GroupBy in your query to filter out the
duplicates.

 

[John Verhagen]

Assuming your table is called tblInvoice, with fields InvoiceNumber [Text],
InvoiceAmount, then try the following:

SELECT tblInvoice.InvoiceNumber,
[InvoiceAmount]/DCount("InvoiceNumber","tblInvoice","InvoiceNumber='" &
[tblInvoice].[InvoiceNumber] & "'") AS InvoiceCalc
FROM tblInvoice;