Max Problems!

[mmck1964]

I have a table that has 2 fields Account Number and Extension. I am trying to make a query to give me the account number with the largest Extension number.

[Account Number] [Extension]
1 15
1 18
1 55
1 76
2 12
2 46
3 01
3 15

I would like to return:

[Account Number] [Extension]
1 76
2 46
3 15

Thanks!

 

[Lilliabeth]

Use the Totals button to add the Totals row to the query grid

Set this to Group By for Account Number, and set it to Max for Extension.

You cannot have any other fields in the query grid.

 

[lespaul]

or in the SQL pane:

SELECT [Account Number], Max(Extension)
FROM TableName
GROUP BY [Account Number]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design