This database is really an Access database, huh? ;)
You are going to eventually regret those table and column names that contain embedded spaces...
Anyway, let's build up this query a piece at a time, sort of from the inside out.
The first question is how to determine the maximum date for any given [Vehicle ID]. That would be:
Code:
SELECT MAX([Status Date])
FROM [Vehicle Status]
WHERE [Vehicle ID] = <the id you want>
then, we would select the row that has this [Status Date] value, again, for the vehicle ID in question:
Code:
SELECT *
FROM [Vehicle Status]
WHERE [Vehicle ID] = <the id you want>
AND [Status Date] = <the maxiumum we just found above>
These can be combined using the first query as a subquery of the second:
Code:
SELECT *
FROM [Vehicle Status]
WHERE [Vehicle ID] = <the id you want>
AND [Status Date] = (SELECT MAX([Status Date])
FROM [Vehicle Status]
WHERE [Vehicle ID] = <the id you want>)
Of course, you want to do this for ALL the vehicle IDs, not just one at a time, so we'll want to execute the above for each Vehicle ID in the table. This is exactly what a "correlated subquery" is designed to do:
Code:
SELECT Vehicles.[ID], Make, Model, [Status], [Status Date]
FROM [Vehicles]
INNER JOIN [Vehicle Status] VS ON [Vehicles].[ID] = VS.[Vehicle ID]
WHERE [Status Date] = (SELECT MAX([Status Date])
FROM [Vehicle Status]
WHERE [Vehicle Status].[Vehicle ID] = VS.[Vehicle ID])
Note that since we refer to the [Vehicle Status] table twice in the query, once in the JOIN and once in the subquery, we need to distinguish the two references from each other. Thus, we use an alias of the table, VS, in the FROM clause.
Also, this alias tell the subquery to use the value of the [Vehicle ID] from the outer query as the selection predicate in the WHERE clause in the subquery. This is the "correlated" part of a "correlated subquery": the subquery is executed once for each row of the outer query, using the current values of the row being processed.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Help needed with JOIN Query
