D J
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.)
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.)
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
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
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.
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
post the SQL statement of the query you are using
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_NUM BER] = b.[DRAWING_NUMBER]) AND (tbl_Drawings.[REVISION] = b.MaxRev);
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_NUM
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Rey & crystal!
you're welcome ~ happy to help