Selecting latest revision but with criteria...

[mivan002]

I have a table with lots of columns, but most important are ID, drawingno,
and revision. I also have a query and this is where I'm trying to get the
latest revision, but when I tell it Last for the revision it will not give me
last until I tell the drawingno to be last as well. Unfortunately, I need
some of the drawingno's that it's not showing.

Here are examples of the drawingno's that I need to stay, but with the
'Last' the query will only give me the last one:
Ex. A123-XXX (with a "-" in the revision column)
Ex. A123-YYY (with a "-" in the revision column)
Ex. A123-ZZZ (with a "-" in the revision column)

The below are examples of the drawingno's that have revisions in the
drawingno column as well as in the revisions column. These are what I need
to have show only last:
Ex. A123-XXX (with a "-" in the revision column)
Ex. A123-XXXA (with a "A" in the revision column)
Ex. A123-XXXB (with a "B" in the revision column) and etc.

To sum up: my current query has Last for drawingno and last for revision and
group on ID, but this gives me all the Last revisions and also gives the Last
drawing numbers. Which is okay for Ex. 2 but not for Ex. 1 - only want
latest drawingno on those that have revisions, but those drawingno's that
don't have revisions to them I need them to stay.

Sorry so long, but wanted to make sure I explained everything.
Thank you in advance.

 

[Ken Snell MVP]

A generic query to show how you use a subquery to do what you seek:

SELECT TableName.DrawingNo, TableName.Revision
FROM TableName
WHERE TableName.Revision =
(SELECT Max(T.Revision) As MR
FROM TableName AS T
WHERE T.DrawingNo = TableName.DrawingNo);

 

[mivan002]

When I do that, I only get one record. I need all of the records to show up
exept for those that have revisions to them and only in that case would I
need the latest record to show up.

Thanks.

 

[Ken Snell MVP]

Sorry, but your example data does not give me enough information to suggest
how to do what you want. All of your sample data suggest that you only
wanted the last revision to show.

Show actual data and what results you want, including examples of where
you'd have more than one record for a DrawingNo value.

 

[mivan002]

All of my data is coming from one table and I do want the query to give me
the latest revision, but only the latest revision if there is a revision
there.
So, I have drawingno as
A100-AAA (with rev - and needs to show in query)
A100-BBB (with rev - and needs to show in query)
A100-CCC (with rev - and needs to show in query) because these have a "-" in
the revision column and because all three are different drawings.
BUT
A200-XXX (with rev - and does NOT need to show because there is the next one)
A200-XXXA (with rev A and does NOT need to show)
A200-XXXB (with rev B and DOES need to show because it's the last rev of
this drawing) In this last example the drawingno is the same only the last
letter is different which corresponds to the revision. Whereas the first
example all three drawings are different and therefore need to show in the
query. Hopefully this explains it a bit more.

Thank you again for your time.

 

[John Spencer]

IF your drawing numbers are exactly 8 characters long (not counting a
revision number).

SELECT Left(DrawingNo,8) as BaseNumber, Max(DrawingNo)
FROM YourTable
GROUP BY Left(DrawingNo,8)

A better design would be to store the base number in one field and the
revision designation in a second field.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[mivan002]

Unfortunately, there are some that are 8 characters and others that are 10
and basically no consistency. But really like you said the drawingno's that
have a revision to them have a revision in the drawingno column as well as in
the revision column. So basically I would only need to state that if the
revision column contains anything other than a "-" in it to take the latest
revision and give me that. But then is what gets complicated, the first
revision to any drawing has a "-" as the first revision, so I would need
something that says if there are duplicate drawingno's minus the last A or B
or C or etc. then take the last one of those, else leave all the other
drawingno's that have "-" in the revision.

 

[Ken Snell MVP]

To assist us with helping you better, post the structure of this table that
contains the data... you keep referring to different "columns" (fields), but
the data that you've shown always appear to be concatentations of those
fields? Show us the table structure and the actual data in the various
fields in the table.

 

[mivan002]

I cannot show the data in the table, it is against company policy. I am not
trying to be confusing... there are only two columns that I've been talking
about and those are the drawingno and the revision. The drawingno has
different variations of drawing numbers and isn't consistent at all, the only
consistency is that if there is a revision to a drawing, on the end of the
drawingno there is a A or B and the same A or B in the revision column. If
there is no revision done to the drawing, then in the drawingno its just the
drawingno itself and a - for the revision.

If you could just help me with how to write something to get the query to
look for duplicates in the first few characters of the drawingno and if there
are duplicates and some of those include A or B or C on the end then to get
the last one, if not to ignore them and move on.

Thank you.

 

[John Spencer]

First query to get just the base numbers. Saved as qBaseNumbers.

SELECT DrawingNumber
FROM DrawingsTable
WHERE Revision = "-"

Now use that query and the drawings table to get the latest (max)
revision. Something like the following.

SELECT Max(DrawingNumber) as MaxDrawing
FROM DrawingsTable as T INNER JOIN qBaseNumbers as Q
ON T.DrawingNumber Like Q.DrawingNumber & "?"
GROUP BY Q.DrawingNumber

This can probably be done in one query if your field and table names
contain ONLY letters, numbers, and the underscore character.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[mivan002]

Thank you, that worked like a charm for the revisions.
But I have one last piece of a puzzle that needs to be done.
So, I got the latest record(from your help below) but now I need to combine
both queries into one column so that I can see:
1. All drawing numbers from max query and all from qBaseNumbers query
combined in one column
2. But the base drawing numbers from the qBaseNumbers query, I only need
those that did not have a rev to them. Basically all drawing numbers from
the max query (#2 below) and all drawing numbers from qBaseNumbers query
Except for the drawing numbers that are the same as the maxdrawing numbers
minus the last character (which is the base number for the revision number).

Again thank you for all of your help on this.

 

[John Spencer]

Try modifying the query to read as follows. As long as you don't have
numbers that can include other numbers this should work.

So you can't have drawing numbers like
A123-xxx
A123-xx
A12

You will end up with false matches. A12 would match A123-xxx and
A123-xx and therefore you would get wrong results for A12 since its
maximum match value would be A123-xxx

SELECT Max(DrawingNumber) as MaxDrawing
FROM DrawingsTable as T INNER JOIN qBaseNumbers as Q
ON T.DrawingNumber Like Q.DrawingNumber & "*"
GROUP BY Q.DrawingNumber


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[mivan002]

Most of the drawing numbers are different but there are some that are
similar, but the only similarity is that the last three or four characters
would be the difference.
Example would be:
A001-XXX
A001-YYY
A001-ZZZ all three of these are the same drawing, but the last three (in
this case and most cases) characters are different.

But there are also data like:
B002-01-2-XXX
B002-01-1-YYY
B002-01-1-ZZZ
B003-04-WWW
B003-04-WWWA
P01-1234
P02-9876

Trying to deal will all of this data and NOTHING in here is consistent, just
drives me crazy! Sorry, getting frustrated about this.

I will try your example and see what that gives me.

Thanks again.