Query to Return Greatest Record

[Joe4]

Given a table with three fields, Name, Date, and Amount, I want to write a query (in Access Query Builder or SQL, NOT in VBA) that will return the single record with the greatest value in the Amount field.

For example, if my data were:

NAME    DATE    AMOUNT
Adam   6/17/04   100
Bret   7/18/04   200
Cris   8/14/04    50

It would simply return:

Bret   7/18/04   200

It seems to me that this should be easy, and I may be having a brain cramp here.

 

[mdusoe]

Maybe try this:

SELECT tblData.Name, tblData.Date, tblData.Amount
FROM tblData
WHERE (((tblData.Amount)=DMax("Amount","tblData")));


Does that give you what you need?
Mike.

 

[Joe4]

Yes, that does exactly what I want.

Thank you.