Finding Latest Record WHERE multiple values match

[Sequoia via AccessMonster.com]

Howdy folks,

I have an Access database (in Access 2000 format) for tracking AutoCAD
drawing files recieved. I need to build a query to return only the latest
version of a particluar drawing, but I'm stumped as to how to build the
query.

The main table (tblPlans) contains the following fields (plus others):

ID (AutoID)
Store
dwg no
Recd Date
Title
Architect

Each time I receive a file, it becomes a separate record, even if there is
already a previous version of the file in the table already.

I know this is not a fully-normalized database (I do have separate tables
for Store and Architect details), but I'm not sure I want to go through the
hassel of "atomizing" the data, then have to build forms, subforms,
queries, reports, etc. to tie it back together so it makes sense to humans.

What I need to do with this query is to find the latest version of each
drawing, i.e., the most recent

[Recd Date]

WHERE

[Store], [dwg no], [Title], and [Architect]

all match

so if I have

"1","XXX","A1.0","11/29/04","Floor Plan","ZZZ"
"2","XXX","A1.0","11/30/04","Floor Plan","ZZZ"
"3","XXX","A1.0","12/12/04","Floor Plan","ZZZ"
"4","YYY","A1.1","10/29/04","Mezzanine Plan","QQQ"
"5","YYY","A1.1","11/15/04","Mezzanine Plan","QQQ"
"6","YYY","A1.1","12/06/04","Mezzanine Plan","QQQ"

ONLY records 3 and 6 are returned by the query.

I'm not sure how to get there.

Thanks for any help you can offer.

Thanks,

- Sequoia

 

[John Spencer (MVP)]

SELECT TblPlans.*
FROM TblPlans as T
WHERE T.[Recd Date] =
(SELECT Max(X.[Recd Date])
From TblPlans as X
WHERE X.Store = T.Store AND
X.[dwg no] = T.[dwg no] AND
X.Title = T.Title AND
X.Architect = T.Architect)

A two query version may prove faster than the coordinated sub-query version
above. A two-query solution would be

SELECT Store, [Dwg No], Title, Architect, Max([Recd Date]) as LastDrawDate
FROM TblPlans
GROUP BY Store, [Dwg No], Title, Architect

Save that as QueryOne and use it in a second query joined to your original
table.

SELECT T.*
FROM TblPlans as T INNER JOIN QueryOne as X
ON T.Store = X.Store AND
T.[dwg no] = X.[dwg no] AND
T.Title = X.Title AND
T.Architect = X.Architect)

 

[Sequoia via AccessMonster.com]

John,

Thanks for the quick response. Unfortunately, I was interrupted several
times while trying out your suggestions (and now I have to go on to another
project, but I'm stealing some time to compose this respone).

With the first, I keep getting the error message "The Microsoft Jet
database engine does not recognize "TblPlans.*" as a valid field name or
expression."

The first part of the second query actually works, and gives me only the
plans with latest Recd Date ... until I try to add in the other date fields
(Plan Date, Delta Date) ... then it displays all records, or at least a lot
more than I need.

The second part of the two-part query fails on my computer with the message
"Syntax error in FROM clause".

I'm not very familar w/ SQL, so I couldn't debug this on my own.

Thanks much.

- Sequoia

 

[Sequoia via AccessMonster.com]

Ooopppssss,

Turns out that the syntax error on 2nd part of the 2nd query was extra
closing parenthesis at the end of the statement. I removed that (just 'cuz
it didn't look right, an unpaired closing paren and all) and it ran, but it
returned all records, not just the most recent ones.

Thanks in advance,

- Sequoia

 

[John Spencer (MVP)]

Sorry. I left out part of the join, we needed the Recd Date joined to the Last DrawDate

SELECT Store, [Dwg No], Title, Architect, Max([Recd Date]) as LastDrawDate
FROM TblPlans
GROUP BY Store, [Dwg No], Title, Architect

Save that as QueryOne and use it in a second query joined to your original
table.

SELECT T.*
FROM TblPlans as T INNER JOIN QueryOne as X
ON T.Store = X.Store AND
T.[dwg no] = X.[dwg no] AND
T.Title = X.Title AND
T.Architect = X.Architect AND
T.[Recd Date] = X.LastDrawDate

 

[Sequoia via AccessMonster.com]

Thanx!

That worked perfectly; the combined queries return only the most recently
received versions of each drawing.

Can you recommend any books and / or websites from which a non-programmer
may learn more about SQL as it applies to Access (or simply SQL in general)?

I'm not a "real" programmer, but I've written some scripts, macros (WP8.0
and previous), and batch files, and from what I've seen here, SQL seems
much more straight forward than the Access GOOEY interface.

Thanks again,

- Sequoia

 

[John Spencer (MVP)]

One of my favorites is SQL for Mere Mortals. I found it helpful.