Hello all
I have 5 different reservoirs and keep track of the rainfall at all 5.
I want to total all five together and then only show the maximum value on a yearly basis. But I also want to include the individual fields for the reservoirs in the record.
I hope I'm making sense.

DJ

See my answer to the following post. You need the same solution - nested queries.
http://www.access-programmers.co.uk/ubb/Forum3/HTML/002719.html

Thanks Pat.
I have an additional question.
The reservoirs are five different tables (not my design) and I created a query including all five tables and added a expression that Sum the rainfall fields.
My question is can I use your suggestion using the expression as the maximum value

I don't think you'll be able to work this out with just a query. You'll need some VBA also. The problem is that the outer query can't be changed on the fly to join back to a different table depending on which table the highest value was obtained from. I think you'll need to run the inner query that selects the highest value and include in it a literal field that identifies which table provided the Max() value. Then examine the result in VBA and choose which of the 5 Outer queries to run, passing it a parameter that tells it which record to retrieve.