Duplicate Records

[bwalk037]

I have a query that matches amounts to invoices. So when I run the query there can be 5 of the same invoice but different amounts for each of the same invoice.

Is there a way to make the query only return the largest amount for each invoice?

 

[bwalk037]

For example the query could return this:

Invoice # Amount

5890 $23
5890 $29
5890 $45
5890 $67
5890 $258


And I would like it to only return:

Invoice # Amount

5890 $258

 

[MSAccessRookie]

I have a query that matches amounts to invoices. So when I run the query there can be 5 of the same invoice but different amounts for each of the same invoice.

Is there a way to make the query only return the largest amount for each invoice?

You need a Max() and Group By to resolve the issue. Something like the following might work.

SELECT InvoiceNumber, Max(InvoiceAmount) AS MaxOfInvoiceAmount
FROM YourTable
GROUP BY InvoiceNumber;

 

[bwalk037]

Thank you. After playing around with it for a while, I was able to figure out that access would let me do that automatically with its group by and max function in the query design view!!