How To Find Maximums By A Field?

  • Thread starter Thread starter quirkyjoe
  • Start date Start date
Q

quirkyjoe

Say I have an Access table that lloks like this:


Num Test Value
1 1 44
1 2 55
1 3 66
2 1 103
2 2 102
2 3 101

I want to construct a query and find the max "Value" for each Num
field and ignore the
ones that are not maxes. So how do I construct a query and get this
result
using the example:

Num Test Value
1 3 66
2 1 103


Basically I want to go through each Num and find the max Value and
also have it so I can identify which Test the max Value came from.


Thanks!
 
Create a new query in design view.

Add the table. Add the two fields (Num, Value).

Click on the "Totals" button (it looks like a backwards "3" -- it is a greek
sigma).

Keep the "GroupBy" for Num, and change it to "Maximum" for Value.

Run the query.
 
Create a new query in design view.

Add the table. Add the two fields (Num, Value).

Click on the "Totals" button (it looks like a backwards "3" -- it is a greek
sigma).

Keep the "GroupBy" for Num, and change it to "Maximum" for Value.

Run the query.

--
Regards

Jeff Boyce










- Show quoted text -
Click to expand...

Jeff,

Thank you very much.
 
Create a new query in design view.

Add the table. Add the two fields (Num, Value).

Click on the "Totals" button (it looks like a backwards "3" -- it is a greek
sigma).

Keep the "GroupBy" for Num, and change it to "Maximum" for Value.

Run the query.

--
Regards

Jeff Boyce










- Show quoted text -
Click to expand...

Jeff or Anyone,

When I do the totals query it works fine but I want to carry a few
additional fields along from the original table, that is, when it
finds the max of the Value field by Num, I also want to to bring along
the "Test" value as well that is associated with the max Vlaue.
Anyway of doing this within the Totals query of do I have to run a
separate query using the results of the totals query as input? I
could not get the Totals query to give me the Test field.

Thanks!
 
Use the first query and the table in another query. Join num & value by
clicking and dragging from the table to the query the same field. Pull down
your three fields from the table.
 
Back
Top