Getting the most latest Revision (help please)

[Kookai]

I think your two table are similar to my example ones. So, just change the fields names in my queries and add the fields you want to the result.

Yeah Ref1 & ref2 has something to do with revision no....

Like for example...



DocID Ref1 Ref2 Subject Rev Rev date rev status
1 ALD-01 DEL-01 Pipes 0 08-16-10 Preliminary
1 ALD-01 DEL-01 Pipes 1 08-17-10 Preliminary

[weekend00]

I created 2 table as your screenshot, if there is any error when you open the query, it maybe some typepo of field names, please check the field names.

I did not find any date field to identify the "most recent" revision, so I assume the max pkDocumentDrawingRevID is the most recent one.

Step 1: Create query "getMostRecentRev" as:

SELECT Max(tblDocumentDrawingRev.pkDocumentDrawingRevID) AS MaxOfpkDocumentDrawingRevID, tblDocumentDrawingRev.fkDocumentDrawingID
FROM tblDocumentDrawingRev
GROUP BY tblDocumentDrawingRev.fkDocumentDrawingID;

Step 2: Create query "Result" as:

SELECT tblDocumentDrawingRev.*, tblDocumentDrawings.*
FROM (getMostRecentRev INNER JOIN tblDocumentDrawingRev ON getMostRecentRev.MaxOfpkDocumentDrawingRevID = tblDocumentDrawingRev.pkDocumentDrawingRevID) INNER JOIN tblDocumentDrawings ON getMostRecentRev.fkDocumentDrawingID = tblDocumentDrawings.pkDocumentDrawingID;

open query "Result" to get the information of latest revision or use this query as data source of your report.

[maximus]

check the sample

[Kookai]

Hi Maximus,

Please find attached thanks... ur help is much appreciated