Largest field based on another field

  • Thread starter Thread starter Richard Coutts
  • Start date Start date
R

Richard Coutts

I have a table of Jobs and Invoice numbers. E.g.,

Smith 1
Jones 1
Smith 2
Smith 3
Henry 1
Jones 2
Jones 3
Henry 2
Jones 4

I need a query that will list just the largest Invoice number for each Job. E.g.,

Smith 3
Jones 4
Henry 2

Thanks!
Rich
 
1. Create a query into this table.

2. In query design view, depress the Total button on the toolbar.
Access adds a Total for to the grid.

3. Drag the name field into the grid.
Access Group By in the Total row under this field.

4. Drag the invoice number field into the grid.
In the Total row under this field, choose Max.
 
SELECT Table1.Name, Max(Table1.[Invoice Number]) AS
[MaxOfInvoice Number]
FROM Table1
GROUP BY Table1.Name;
 
Back
Top