Determine Max Record

[Joe]

I have 4 fields in a table: Need ID, Cost ID, Estimated Hours, and
Ranking.

Sample data would be:
CostID NeedID SumEstimateHours Ranking
359 372 150 2
360 372 50 1

I need to write a query that will return the Max ranking record - in
this case the Ranking = 2 record.

 

[John W. Vinson]

I have 4 fields in a table: Need ID, Cost ID, Estimated Hours, and
Ranking.

Sample data would be:
CostID NeedID SumEstimateHours Ranking
359 372 150 2
360 372 50 1

I need to write a query that will return the Max ranking record - in
this case the Ranking = 2 record.

A Subquery will do this for you. Create a query based on the table and put

=(SELECT Max([Ranking] FROM yourtable)

on the Ranking field. The parentheses are eseential; you can put additional
criteria within the parentheses if needed; e.g. if you want just the maximum
ranking for a particular NeedID, the subquery might be

=(SELECT Max([Ranking] FROM yourtable AS X WHERE X.NeedID = yourtable.NeedID)

to make it a "correlated" subquery.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com