Help with Totals (1 Viewer)

Hello,

I have a table with the following fields:

Invoice
Division
Amount

The Invoice can repeat (and often does) as each invoice can have multiple divisions. Divisions can also repeat for a single invoice as separate records have different Amounts.

What I need is a query that will tell me which Division for each Invoice has the highest Amount:

ID Invoice Division Amount
1 12345 ABC 500.00
2 12345 ABC 250.00
3 12345 DEF 650.00
4 12345 GHI 700.00
5 67890 ABC 250.00
6 67890 DEF 450.00
7 67890 GHI 150.00

The desired query result would be this:

ID Invoice Division Amount
??? 12345 ABC 750.00
??? 67890 DEF 450.00

The actual source data has many more fields (that I do not need in the query results), and consists of 150K+ records so query performance is also a concern, although I will take what I can get at this point.

I have reached the limit of my experience with trying to resolve this and would much appreciate some assistance from someone with more skill at this than myself.

:banghead:

Thanks in advance.

You can accomplish this with two queries.

First you will need a Totals query that sums the Amount by Invoice for each Division in descending order. You need to leave the ID field out of this query in order to sum correctly. SQL would look like the following (correct for your actual table/field names);

SELECT Table1.Invoice, Table1.Division, Sum(Table1.Amount) AS SumOfAmount
FROM Table1
GROUP BY Table1.Invoice, Table1.Division
ORDER BY Sum(Table1.Amount) DESC;

Then a second query based on the first. In the second query you would be referencing the first query twice by means of a sub query. When you do this you have to apply an alias for the second reference to the name of the duplicate data source (in this case the first query). In the example below I have used the word Dupe as the alias, but you can use any alias name you like. SQL for the second query would look like the following (again, correct for your actual query / field names);

SELECT Query1.Invoice, Query1.Division, Query1.SumOfAmount
FROM Query1
WHERE Query1.SumOfAmount In (Select Top 1 SumOfAmount From Query1 As Dupe Where Dupe.Invoice = Query1.Invoice);

You can find more information on sub queries here