Filter records with "same" names by most current date

[LianeMT]

I'm working on a db that manages documents/drawings. Each document/drawing
has a DocNumber assigned to it. If there's a revision to the
document/drawing, it will have a RevNumber added to the DocNumber. I have a
query that pulls released documents and contains the information: DocNumber,
RevNumber, ReleaseDate.

I'm trying to work out a filter to only report the latest revision of the
document, and not list all revisions. For example, if my query pulls the
following documents:

DocNumber RevNumber ReleaseDate
311D5000 1/1/2008
311D5000A A 2/2/2008
311D5000B B 2/15/2008
311D5001 2/11/2008
311D5002 1/06/2008

I really only want to see the following:

DocNumber RevNumber ReleaseDate
311D5000B B 2/15/2008
311D5001 2/11/2008
311D5002 1/06/2008

The first two occurences of 311D5000 (the original and Rev A) should be
discarded from the query (but not removed from the table that holds the data).

Any suggestions would be welcome. Thank you!
LianeMT

 

[Dorian]

Assuming latest revision has the latest revision date, could you just not
take the record with the latest revision date for each docnumber.
You don't want to have the rev number as part of the docnumber!

-Dorian

 

[LianeMT]

Unfortunately, I am stuck with the DocNumber format including the Rev, as a
crude method to prevent duplicate records with the same DocNumber while
permitting the revision process to include the original data relevant to the
document and a means to track changes without losing history.

This is why I'm seeking some method to filter the DocNumber names (LEFT?)
and compare the "base DocNumber" (311D5000) by the dates...

 

[Clifford Bass]

Hi Liane,

Try (assumes root DocNumber always eight characters and newer revisions
always have later release date):

select DocNumber, RevNumber, ReleaseDate
from tblDocuments as A
where not exists
(select * from tblDocuments as B
where Left$(B.DocNumber, 8) = Left$(A.DocNumber, 8) and B.ReleaseDate >
A.ReleaseDate);

Clifford Bass

 

[LianeMT]

Clifford,

Both assumptions hold true. This works like a charm! Exactly what I was
looking for and fairly easy to implement!

THANK YOU SO MUCH!
~Liane

 

[Clifford Bass]

Hi Liane,

Glad to help!

Clifford Bass