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.

On Wed, 9 Feb 2011 05:35:33 -0800 (PST), Joe <(E-Mail Removed)> wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com