Select most recent date

[Rick Dunmire]

Hello everyone
I need to select the most recent date from a recordset like this.
A customer has 3 inspection dates I need the most recent date for that
customer.
Here is the sql statement as of now.

PARAMETERS Combo1 Short, Combo2 Short;
SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state, Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Vehicle.status,
Inspection.inspection_date
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle =
Inspection.fk_vehicle
WHERE (((Vehicle.status)="0") AND
((([Vehicle].[license_plate_expiration_month])-(([Vehicle].[license_plate_ex
piration_month])<[Combo1])*100) Between [Combo1] And
[Combo2]-([Combo2]<[Combo1])*100) AND
((nz([Vehicle].[license_plate_expiration_month],0))>0))
ORDER BY
(Vehicle.license_plate_expiration_month)-((Vehicle.license_plate_expiration_
month)<[Combo1])*100;

I guess I need some kind of WHERE clause in there about the most recent
Inspection.inspection_date.
What am I in need of?
Thanks
Rick Dunmire

 

[Tom Ellison]

Dear Rick:

The technique is called a Correlated Subquery. This is a subquery
that returns a single value, the maximum of the date column in the
desired table from those rows for the desired entity. You can then
use a WHERE clause (as you correctly guessed) to filter to only the
row with that most recent date.

Before beginning to do this, do you know whether there can be more
than one Inspection for the same vehicle on the same date? If your
database allows this, then there is no "most recent inspection"
because there can be two or more "most recent inspections." And even
though it may be the case that there is not now any case where there
are multiple inspections on the same date, you can bet it will come
back and bite you later.

Now I'm going to rearrange your query to make it more readable for me
and give you the missing part you need:

PARAMETERS Combo1 Short, Combo2 Short;
SELECT V.registrant_name, C.street_address_1, C.street_address_2,
C.city, C.state, C.zip_code, V.vehicle_year, V.vehicle_make,
V.vehicle_model, V.license_plate_expiration_month, V.status,
I.inspection_date
FROM (Customer C
INNER JOIN Vehicle V ON C.pk_customer = V.fk_customer)
LEFT JOIN Inspection I ON V.pk_vehicle = I.fk_vehicle
WHERE V.status = "0"
AND I.inspection_date = (SELECT MAX(I1.inspection_date)
FROM Inspection I1 WHERE I1.fk_vehicle = V.pk_vehicle)

I've left off your criterion for license expiration and ordering.
Frankly, I couldn't follow this part. But I expect you can add this
back in fairly easily after you have the above working.

Now that I have that much done and I'm looking back over it, I see now
that it is largely unnecessary. You aren't returning any values from
the Inspection table other than the inspection_date.

First this means that it doesn't matter how many inspecitons you have
on the most recent inspection date. All you want to see is the date.
You're not asking for the name of the person who performed the
inspection or any such possible related information. The whole thing
could be reduced to a simpler form of query using just an aggregate
function:

PARAMETERS Combo1 Short, Combo2 Short;
SELECT V.registrant_name, C.street_address_1, C.street_address_2,
C.city, C.state, C.zip_code, V.vehicle_year, V.vehicle_make,
V.vehicle_model, V.license_plate_expiration_month, V.status,
MAX(I.inspection_date)
FROM (Customer C
INNER JOIN Vehicle V ON C.pk_customer = V.fk_customer)
LEFT JOIN Inspection I ON V.pk_vehicle = I.fk_vehicle
WHERE V.status = "0"
GROUP BY V.registrant_name, C.street_address_1, C.street_address_2,
C.city, C.state, C.zip_code, V.vehicle_year, V.vehicle_make,
V.vehicle_model, V.license_plate_expiration_month, V.status

You still need to add your other stuff back on, but this gives the
same results, doesn't it? And probably faster, too!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hello everyone
I need to select the most recent date from a recordset like this.
A customer has 3 inspection dates I need the most recent date for that
customer.
Here is the sql statement as of now.

PARAMETERS Combo1 Short, Combo2 Short;
SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state, Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Vehicle.status,
Inspection.inspection_date
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle =
Inspection.fk_vehicle
WHERE (((Vehicle.status)="0") AND
((([Vehicle].[license_plate_expiration_month])-(([Vehicle].[license_plate_ex
piration_month])<[Combo1])*100) Between [Combo1] And
[Combo2]-([Combo2]<[Combo1])*100) AND
((nz([Vehicle].[license_plate_expiration_month],0))>0))
ORDER BY
(Vehicle.license_plate_expiration_month)-((Vehicle.license_plate_expiration_
month)<[Combo1])*100;

I guess I need some kind of WHERE clause in there about the most recent
Inspection.inspection_date.
What am I in need of?
Thanks
Rick Dunmire

 

[Rick Dunmire]

Thanks Tom
Let me clarify a little here if I can. I tried to use a simple example but I
don't think that is going to explain my exact needs here.
Lets try this.
I own an auto repair shop and we do automobile safety inspections once a
year. I need to get this information from the database so I can send
inspection reminders to my customers according to there inspection records
at our shop.
OK, what I need is to select every vehicle where the status = 0 (this means
its still owned by the customer, not sold or disposed of) and has a previous
inspection date, using the latest inspection date as a parameter (may have
inspections for the last 4 years and I just need the most recent one) where
the sticker_expiration_month = the value between and including combo1 and
combo2 , which is 1 - 12 (that is the WHERE (((Vehicle.status)="0") AND
((([Vehicle].[license_plate_expiration_month])-(([Vehicle].[license_plate_ex
piration_month])<[Combo1])*100) Between [Combo1] And
[Combo2]-([Combo2]<[Combo1])*100)
AND((nz([Vehicle].[license_plate_expiration_month],0))>0)) )
Right now I am using just the license_plate_expiration_month in the above
sql. Now, if there is no previous inspection, then in this case I will
select every vehicle (without a previous inspection) where the
license_plate_expiration_month = the value between and including combo1 and
combo2.

I know this looks odd but aside from the inspection date thing, everything
else works fine. Would a .zip of the database help explain what I am trying
to do? Its not that I am trying to pressure you into looking more into my
problem, its just that sometimes its hard to explain what I am trying to do.

Rick Dunmire
Dunnies Garage

Dear Rick:

The technique is called a Correlated Subquery. This is a subquery
that returns a single value, the maximum of the date column in the
desired table from those rows for the desired entity. You can then
use a WHERE clause (as you correctly guessed) to filter to only the
row with that most recent date.

Before beginning to do this, do you know whether there can be more
than one Inspection for the same vehicle on the same date? If your
database allows this, then there is no "most recent inspection"
because there can be two or more "most recent inspections." And even
though it may be the case that there is not now any case where there
are multiple inspections on the same date, you can bet it will come
back and bite you later.

Now I'm going to rearrange your query to make it more readable for me
and give you the missing part you need:

PARAMETERS Combo1 Short, Combo2 Short;
SELECT V.registrant_name, C.street_address_1, C.street_address_2,
C.city, C.state, C.zip_code, V.vehicle_year, V.vehicle_make,
V.vehicle_model, V.license_plate_expiration_month, V.status,
I.inspection_date
FROM (Customer C
INNER JOIN Vehicle V ON C.pk_customer = V.fk_customer)
LEFT JOIN Inspection I ON V.pk_vehicle = I.fk_vehicle
WHERE V.status = "0"
AND I.inspection_date = (SELECT MAX(I1.inspection_date)
FROM Inspection I1 WHERE I1.fk_vehicle = V.pk_vehicle)

I've left off your criterion for license expiration and ordering.
Frankly, I couldn't follow this part. But I expect you can add this
back in fairly easily after you have the above working.

Now that I have that much done and I'm looking back over it, I see now
that it is largely unnecessary. You aren't returning any values from
the Inspection table other than the inspection_date.

First this means that it doesn't matter how many inspecitons you have
on the most recent inspection date. All you want to see is the date.
You're not asking for the name of the person who performed the
inspection or any such possible related information. The whole thing
could be reduced to a simpler form of query using just an aggregate
function:

PARAMETERS Combo1 Short, Combo2 Short;
SELECT V.registrant_name, C.street_address_1, C.street_address_2,
C.city, C.state, C.zip_code, V.vehicle_year, V.vehicle_make,
V.vehicle_model, V.license_plate_expiration_month, V.status,
MAX(I.inspection_date)
FROM (Customer C
INNER JOIN Vehicle V ON C.pk_customer = V.fk_customer)
LEFT JOIN Inspection I ON V.pk_vehicle = I.fk_vehicle
WHERE V.status = "0"
GROUP BY V.registrant_name, C.street_address_1, C.street_address_2,
C.city, C.state, C.zip_code, V.vehicle_year, V.vehicle_make,
V.vehicle_model, V.license_plate_expiration_month, V.status

You still need to add your other stuff back on, but this gives the
same results, doesn't it? And probably faster, too!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hello everyone
I need to select the most recent date from a recordset like this.
A customer has 3 inspection dates I need the most recent date for that
customer.
Here is the sql statement as of now.

PARAMETERS Combo1 Short, Combo2 Short;
SELECT Vehicle.registrant_name, Customer.street_address_1,
Customer.street_address_2, Customer.city, Customer.state, Customer.zip_code,
Vehicle.vehicle_year, Vehicle.vehicle_make, Vehicle.vehicle_model,
Vehicle.license_plate_expiration_month, Vehicle.status,
Inspection.inspection_date
FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle =
Inspection.fk_vehicle
WHERE (((Vehicle.status)="0") AND
((([Vehicle].[license_plate_expiration_month])-(([Vehicle].[license_plate_e

x

piration_month])<[Combo1])*100) Between [Combo1] And
[Combo2]-([Combo2]<[Combo1])*100) AND
((nz([Vehicle].[license_plate_expiration_month],0))>0))
ORDER BY
(Vehicle.license_plate_expiration_month)-((Vehicle.license_plate_expiration

_

month)<[Combo1])*100;

I guess I need some kind of WHERE clause in there about the most recent
Inspection.inspection_date.
What am I in need of?
Thanks
Rick Dunmire