Largest field based on another field

[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

 

[Allen Browne]

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.

 

[Jimbo]

SELECT Table1.Name, Max(Table1.[Invoice Number]) AS
[MaxOfInvoice Number]
FROM Table1
GROUP BY Table1.Name;