Finding the "most recent" data

[Ed Sorensen]

I have three linked tables
Documents: containing a primary key (DocID) and two text fields (DocNum and
DocName)
Revisions: containing a primary key (RevID), a link to the document table
(DocID) and a text field (Rev)
Actions: containing a primary key (Action ID), a link to the Rev table
(RevID) and a date field (ActionDate)

For each document, I want to find the newest revision and the date on which
the last action was performed on the revision.

The revisions are identified by an alphanumeric character (A through Z and 0
to 99) Revisions 0 through 99 are newer than revisions A through Z).

How do I set up a query that gives me all this information?

 

[Maelinar]

Have you tried altering your query to group by: Last ?

 

[John Spencer]

First problem is to identify the newest Rev for each Doc
So are your rev number A, B, C, ...x, y, z, 00, 01, 02, 03,...,99 or are
they more like A, B, C, ...x, y, z, 0, 1, 2, 3,...,99 ?

Assuming the former you can use something like the following to force the
format.
IIF(IsNumeric(RevID),RevID, "000" & RevID)

If the latter then
IIF(IsNumeric(RevID),Format(RevID,"00"), "000" & RevID)

Assuming the former
SELECT R.DocID
, Max(IIF(IsNumeric(RevID),RevID,"000" & RevID)) asMaxRev
, IIF(MaxRev Like "000*",Mid(MaxRev,4),MaxRev) as RevNum
FROM Revisions as R
GROUP BY R.DocID

IF that works then you can go onto the next step which is to identify the
last action for that Rev and DocID
SELECT qAbove.DocID, A.RevID, Max(A.ActionDate) as LastActionDate
FROM qAbove INNER JOIN Actions as A
ON qAbove.RevNum = A.RevID
GROUP BY qAbove.DocID, A.RevID

Now you can use that to get your final result by joining the Doc table to
this final query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..