Most Current Rev Only

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I need to build a query that collects information about the most current
revision only. For example...

Base Revision Date
A A 2000
B 2002
2 2003

B 1 2000
2 2003
3 2005

I need the results to bring back A - 2 - 2003 and B - 3 - 2005 only. Any
suggestions? (I am trying to stay away from unions.)

Thanks,
Jessica
 
When you create your query change it to a crosstab query and then
change it back to a select query and you will have a new line "Total"
as one of your drop down options for your query. Select Max in the
drop down for your "Date" column. That will give you the Max date
which will be the most current year. I hope this helps.

Micky
 
Dear Jessica:

I assume your sample data is really:

Base Revision Date
A A 2000
A B 2002
A 2 2003
B 1 2000
B 2 2003
B 3 2005

SELECT Base, Revision, [Date]
FROM YourTable T
WHERE [Date] = (
SELECT MAX([Date])
FROM YourTable T1
WHERE T1.Base = T.Base)

If you had another row A / X / 2003, then you'd have two "most recent"
revisions for Base A, and both would be returned. This makes lots of sense,
but is not always obvious till you think about it. If Base and Date are not
unique taken together, then this can happen.

What I have provided is a typical correlated subquery. You can look up this
term, and also "alias" to study the techniques used. A simple totals query
would return the Base / Date for the most recent, but to return the Revision
value of that row (or those rows) you must provide a filter to do that, with
the Date being determined by the subquery. Understand?

Tom Ellison
 
Talk about an UGLY problem and it looks so straight forward at the beginning
as a simple totals query. First off I'm assuming that the Revision field
holds the key to the last revision as you could have more than one revision
in a year. If I'm wrong about that please say so.

The problem is that when you sort ABC's with 123's, the numbers are smaller
than the alphabetical characters. In other words 2 smaller than A when doing
a Max to get the last revision. So I created a query that looks at the ASCII
value of the data and then plays with it some.

SELECT Revisions.Base,
Max(IIf(Asc([Revision])>64,"1"+[Revision],[Revision])) AS Expr1
FROM Revisions
GROUP BY Revisions.Base;

Well that inserts a character 1 in front of the Revision number so it grabs
the right one. Still doesn't get us the year and messes up the data some. So
I created another query based on the previous query plus the base table.

SELECT Revisions.Base,
Revisions.Revision,
Revisions.Date
FROM qryRevisions INNER JOIN Revisions ON qryRevisions.Base = Revisions.Base
WHERE (((Revisions.Revision)=Mid([qryRevisions].[Expr1],2)))
OR (((Revisions.Revision)=[qryRevisions].[Expr1]));

Eewww! That cleans up the added 1s and adds in the Year. Still I bet it has
more holes in it that my socks.

Were I you, I'd change the ABCs to 123s or the other way around. If
converted to 123s make the field a number data type with single or double so
that you could add in decimal changes. Then you query would be very simple to
do.
 
Back
Top