Max in a Query

[Bob V]

Trying to get just the 1 record of a OwnerID with the highest number in
there BillID1
Thanks for any Help....Bob
My Attempt
SELECT tblAccountStatus.PaidAmount, tblAccountStatus.OwnerID,
Max(tblAccountStatus.BillID1) AS MaxOfBillID1
FROM tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID =
tblOwnerInfo.OwnerID
GROUP BY tblAccountStatus.PaidAmount, tblAccountStatus.OwnerID;

 

[rico]

This site has a very good MAx tutorial:
http://www.techonthenet.com/sql/max.php

HTH

Rico

 

[Bob V]

Thanks Rico
worked it out
SELECT rh.ownerID, rh.PaidAmount, rh.BillDate
FROM tblAccountStatus AS rh, [SELECT max(Billdate) as maxdate, OwnerID
FROM tblAccountStatus
GROUP BY OwnerID]. AS maxresults
WHERE rh.OwnerID = maxresults.OwnerID
AND rh.BillDate= maxresults.maxdate;