Displaying newest subrecord of every record 2

[pkahlo]

I'm not sure if this is possible in a query. I have a table of documents and a related table of document revisions. I want to display only the newest revision of every document.

Can I do this in a query or do I have to do something in VBA?

 

[nicsin]

Can you describe the structure of the two tables?

 

[pkahlo]

The Fields that will be used are:

Documents
DocumentID
RevisionID

Revisions
RevisionID
RevisionDate

I only want to select the revisions with the most recent RevisionDate, and only one revision for each document.

 

[Golom]

Really? That table structure looks like you could have different documents with the same revision ID. If that is correct then

   Select A.DocumentID, A.RevisionID, MAX(B.RevisionDate) As [LastRevision]
   From Documents A INNER JOIN Revisions B 
        ON A.RevisionID = B.RevisionID
   GROUP BY A.DocumentID, A.RevisionID

 

[nicsin]

I agree with Golom. It seems that the DOCUMENTS table should be the master and thus the REVISIONS table should contain the DOCUMENTID. In that case:

Documents
DocumentID

Revisions
RevisionID
DocumentID
RevisionDate

and the SQL:


SELECT
A.DocumentID,
A.RevisionID,
A.RevisionDate
FROM
Revisions A
WHERE
A.RevisionDate = (SELECT
MAX(B.RevisionDate)
FROM
Revisions B
WHERE
A.DocumentID = B.DocumentID)



I haven't tested it but it should work.

 

[pkahlo]

Thanks guys! I actually had the table setup the way you recommended, I just typed it in wrong.

 

[nicsin]

Thanx pkahlo