Link to home
Start Free TrialLog in
Avatar of D J
D JFlag for United States of America

asked on

MS Access How can I complete my Max, Group by query?

I have a document table:
Doc Num, Doc Rev, Doc Title
123            A               Project 1
123            B               Project 1
Some documents have multiple revs

I created a Totals query which displays only the latest Rev: (Rev Max     Doc Group by)
123            B               Project 1

How do I add the other fields to this Max query? (title, ID, etc.)
Avatar of Bill Bach
Bill Bach
Flag of United States of America image

If all data is in the same table, then you simply add the other fields to the SELECT and GROUP BY clauses.  Otherwise, you can JOIN in a second copy of the table and select fields from that copy.
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

if you want other fields on the same record (title, ID, etc) for the latest revision, one way is to create another query based on that Totals query and join in the document table on Doc Num and Doc Rev -- then show whatever else you want
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D J

ASKER

Thanks crystal for finishing up Rey's statement works great!

To create an inner join (like the above select statement) do you have to be in SQL view?
you're welcome
you need to be in SQL view to define the subquery -- then you can switch to Design View to pick fields and set/change the relationship
oops, thanx Crystal, somebody rang the door bell and I have to get the door
Avatar of D J

ASKER

Thanks crystal,
I'm receiving an error when applying this query to my report:
The specified field Doc Num could refer to more than one table listed in the FROM clause of your SQL statement.
you said <I have a document table:>

post the SQL statement of the query you are using
Avatar of D J

ASKER

Here you go Rey:

SELECT *
FROM tbl_Drawings INNER JOIN (SELECT a.[DRAWING_NUMBER], max(a.[REVISION]) AS MaxRev FROM tbl_Drawings AS a GROUP BY a.[DRAWING_NUMBER])  AS b ON (tbl_Drawings.[DRAWING_NUMBER] = b.[DRAWING_NUMBER]) AND (tbl_Drawings.[REVISION] = b.MaxRev);
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D J

ASKER

Thanks Rey & crystal!
you're welcome ~ happy to help