Query for duplicate, then perform calculation.

  • Thread starter Thread starter Lisa
  • Start date Start date
L

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
 
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
Click to expand...
use SELECT DISTINCT (if you're comfortable writing your
own SQL) or use GroupBy in your query to filter out the
duplicates.
 
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;
 
Back
Top