Filter out newest information from two columns

[Erika-help?]

Hello Everyone,

Please help!

I am trying to filter out the latest drawing recieved in a table/query by
using a document number and a revision (simple, huh?); however, my problem is
displayed below:
ColumnA ColumnB
Drawing#: Revision:
drawing1 A
drawing1 1

I would like to filter out the latest revision for each drawing without
using the dates (drawings come in at different times with different
revisions). I would look for the highest number or highest letter but that
would leave out the drawings that only had a few revisions. I also don't want
to replace the drawing number because I need to track the information for all
revisions.

Any ideas?

 

[Jeff Boyce]

I don't have a very clear picture yet on how YOU are defining "latest". If
I were trying to find "latest", I'd use "most recent date", but you seem to
be ruling that option out.

How would you explain to an intern how to determine which record was the
latest?

Now take that (explanation) to the next level ... MS Access is not nearly as
intelligent as an intern!

If you aren't using something Access already knows how to do (e.g., sort by
date), you'll have to provide a procedure that explains to Access precisely
how to do what you want.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

If all you want is the identifier for the drawing in question, use a Totals
query along the lines of:

SELECT ColumnA, Max(ColumnB) As MaxB
FROM MyTable
GROUP BY ColumnA

If you want to retrieve the details of those drawings, join your table to
the query above:

SELECT Field1, Field2, ColumnA, ColumnB, ...
FROM MyTable INNER JOIN
(SELECT ColumnA, Max(ColumnB) As MaxB
FROM MyTable
GROUP BY ColumnA) AS Subquery
ON MyTable.ColumnA = Subquery.ColumnA
AND MyTable.ColumnB = Subquery.MaxB

 

[Erika-help?]

Thank you Douglas. I will try that.

If all you want is the identifier for the drawing in question, use a Totals
query along the lines of:

SELECT ColumnA, Max(ColumnB) As MaxB
FROM MyTable
GROUP BY ColumnA

If you want to retrieve the details of those drawings, join your table to
the query above:

SELECT Field1, Field2, ColumnA, ColumnB, ...
FROM MyTable INNER JOIN
(SELECT ColumnA, Max(ColumnB) As MaxB
FROM MyTable
GROUP BY ColumnA) AS Subquery
ON MyTable.ColumnA = Subquery.ColumnA
AND MyTable.ColumnB = Subquery.MaxB

 

[Erika-help?]

Hello Jeff,
I guess I wasn't clear... The most recent drawing is the drawing with the
highest revision number(i.e Drawing1 Rev 2 is a revision of Drawing1 Rev1, so
it is the most recent); however, sometimes they just have letters (in place
of the number revision and the most recent would be the highest letter). The
dates are all on the same column so if i filter ascending/ decending it would
only filter the drawings entered on the most recent date and omit the rest
that were updated a few days earlier).

Thank you,

Erika