Access: Query to retrieve max value but display all columns from original table in Access 2003/XP/2000/97
Also learn how to create a query to retrieve the max value but display all columns from the original table in Access 2007.
Question: In Access 2003/XP/2000/97, I have a table with following columns:
col1 col2 col3 a01 5 Pete a01 4 John a01 3 Don a02 7 Elsa a02 8 Rick a03 9 Betty a03 10 Vera a03 8 Ronald
With a query, I want to reach the records with the highest number in col2 as follows:
col1 col2 col3 a01 5 Pete a02 8 Rick a03 10 Vera
I want to get those records and all of the columns in the records, when I try to do this with:
Select MAX(col 2), col1 from Table1 group by col1;
I only get two columns, col1 and col2. I want all columns, do you know where I'm going wrong?
Answer: To do this, you'll need to create two Access queries to retrieve your desired results. We've created a sample Access database that you can download that contains the queries used to demonstrate this example.
Download version in Access 2000
First, you need to create a query that retrieves the highest number in col2 for each col1 value. We've created a query called "Step 1 - Retrieve Max col2 value for each col1".
The results for this query are as follows:
Now, we are still missing the col3 value from our result set. To retrieve all columns but still only the highest col2 values, we need to create a second query that takes the results from the first query and joins back to the original table.
We've called this query as "Step 2 - Final results with all columns". The trick to this query is to make sure that you join col1 in Table1 against col1 in our first query. But also join col2 in Table1 against the "MaxOfcol2" in the first query.
Now when we run this second query, we get our desired results.
