Looking at specific range of numbers and returning the highest found

**SteinerKD** · 02-26-2013, 05:25 PM

Working on a query where I need to look at a table containing logs, one column in it contains numbers and I need to look at it and find specific numbers in it and if found return the highest of them.

What I am doing is looking at the log of awards handed out and each award exist in several grades (each with a unique ID), what I'm trying to do for each award type is get back the highest grade received for each member.

So far I've been playing around with IIf formulas but I don't think that is the way to go and would love some advice on how to set things up.

I use two tables, the member table containing the member id linking them together and the award log table containing a column with award IDs (each award type I will be looking for can have 3-6 different grades so I need to find the highest of those 6 specific numbers present).

Am I making sense?

**RHCPgergo** · 02-26-2013, 05:28 PM

Hey,

posting the workbook would help enormously.

**SteinerKD** · 02-26-2013, 05:37 PM

Originally Posted by RHCPgergo

Hey,

posting the workbook would help enormously.

Sorry for being ignorant, what (is a) workbook? isn't that a Excel thing?
Hope I don't sound rude, just don't understand exactly what you are asking me to provide.

**alansidman** · 02-26-2013, 06:04 PM

Not sure if I am visualizing properly, but I think that you need to have an aggregate query. Here is a link on aggregate queries.

http://www.techonthenet.com/access/f...umeric/max.php

Post back if you need more assistance or if I am on the wrong track

**SteinerKD** · 02-26-2013, 06:34 PM

That looks interesting if I can limit the numbers it actually looks for so I don't get just the highest number for the user in the log. That column will contains one or more occasions of numbers between 1-139, what I need to look for is the occurrence of 3, 4 and 5 and return the highest one of them found.

**alansidman** · 02-26-2013, 07:31 PM

You may have to do this with a series of queries. First create a new field and put an IIF statement to only return the numbers you wish. Then take that query and make it the source for your aggregate query.

I hope I am understanding this correctly.

**SteinerKD** · 02-26-2013, 10:10 PM

Brilliant! Thank you, I think that actually solved it in one querie!

I use:
Test: IIf([aid]=4;4;IIf([aid]=5;5;IIf([aid]=6;6;0)))
And then use max on it and I get one answer back from each member with the highest value.