Max query returning multiple fields

[Guest]

I am not sure why I am having so much dificulty right now...

I have a table with 3 fields and 200,000 records.

Fields are Supplier, Unit, Spend

Supplier is the Supplier's Name
Unit is the Unit that spent money
Spend is the amount of money that the Unit Spent with the Supplier

The table is already grouped

I would like to query this table to find out which unit spent the most with
each supplier...Results should have each supplier listed once along with the
unit that spent the most money with them and then how much that unit spent.

I am certain this is probably pretty easy and I am missing something, but
any assistance would be GREATLY Appreciated. Thanks in advance =)

-John

 

[Marshall Barton]

I am not sure why I am having so much dificulty right now...

I have a table with 3 fields and 200,000 records.

Fields are Supplier, Unit, Spend

Supplier is the Supplier's Name
Unit is the Unit that spent money
Spend is the amount of money that the Unit Spent with the Supplier

The table is already grouped

I would like to query this table to find out which unit spent the most with
each supplier...Results should have each supplier listed once along with the
unit that spent the most money with them and then how much that unit spent.

SELECT T.Supplier, T.Unit, T.Spend
FROM table As T
WHERE T.Spend = (
SELECT Max(X.Spend)
FROM table As X
WHERE X.Supplier = T.Supplier)