vehicle maintenance

[Guest]

I'm trying to figure out the best way to go about creating something to track
when vehicles are due for their next maintenance.

I've created the tblVehciles, with the VIN, License, Make, etc, and the
tblMaintenance, which will track the DateofService, Mileage,
ServiceCompleted. Under ServiceCompleted I have a list to choose from, Oil
Change, Tire rotation, Oil/Tire Rotation, Mileage (just a mileage entry), and
Other.

What I want to happen is to create an autoexe Macro that will run every
Monday to pull up a report that will show if any vehicle is coming due for a
change. I know how to do the Macro, just trying to figure out the best way
to construct the query. There will be at least one entry per month for
vehicle where mileage is entered since those are turned in monthly, so that
entry would have Mileage under the ServiceCompleted field. I need to somehow
query to find the last entry with Oil Change, and compare that entry's
mileage to the last entry's mileage. I also need to check that entry's
DateofService to the current date to make sure it hasn't gone past 6 months.
I'd really like to have it warn if it's within 200 miles of going 3000 miles
since the last change or within one month of going 6 months since the last
change.

I'd like to do the same thing for Tire Rotations, but that would just be
every 6,000 miles. Any ideas would be appreciated.

 

[Guest]

You need a ServiceReq table listing the services and interval. Then a
VehicleSvcReq the has vehicle ID, ServiceReq, Method. Method is whether
next service is the last schedule plus interval or last completion plus
interval.
The interval needs to be the lowest common denominator of all services such
as weeks if any one of the services is to be accomplished on a weekly basis -
bi-weekly - monthly - quarterly. All intervals will be multiples of the
selected interval. If fluid checks are weekly and oil change every three
months then oil change would be interval 13 - 13 weeks.

 

[Guest]

If I'm understanding this right, these would be in addition to what I have?
I understand the ServiceReq table since I have done something similiar on a
training database where I put how long it takes for each to expire so the
expiration date will automatically generate on the form. The only thing we
are really tracking for where we'd need a warning is oil changes and tire
rotations, but oil changes need to be checked at either 3000 miles or 6
months....that's where I'm getting into a glitch.

 

[Guest]

There are many ways to design a system for what you want. One way would be to
have a NextServiceDue table. This would contain the vehicle identification,
service identification, and either a date or a mileage. The next service due
table would be updated whenever a vehicle receives a service. It would be a
simple matter to scan this table to determine if any date was past due or if
any vehicle current mileage exceeded the target mileage (assuming you have
that stored in another table).

-Dorian

 

[Michael Gramelspacher]

I guess there are several ways to design your tables. Here is one
possibility:

Option Compare Database
Option Explicit

Sub CreateTables()
With CurrentProject.Connection

.Execute _
"CREATE TABLE Vehicles" & _
" (vin TEXT (17) NOT NULL PRIMARY KEY," & _
" license_nbr TEXT (10) NOT NULL," & _
" license_expire_date DATETIME NOT NULL," & _
" vehicle_type TEXT (10) NOT NULL," & _
" vehicle_model TEXT (20));"

.Execute _
"CREATE TABLE VehicleMileage " & _
" (vin TEXT (17) NOT NULL REFERENCES Vehicles (vin)," & _
" mileage LONG NOT NULL," & _
" reading_date DATETIME NOT NULL," & _
" PRIMARY KEY (vin, reading_date));"

.Execute _
"CREATE TABLE Services" & _
" (service_code TEXT (3) NOT NULL PRIMARY KEY," & _
" service_description TEXT (100) NOT NULL," & _
" service_interval_miles INTEGER NOT NULL," & _
" service_interval_months INTEGER NOT NULL);"

.Execute _
"CREATE TABLE VehicleServices " & _
" (vin TEXT (17) NOT NULL REFERENCES Vehicles (vin)," & _
" service_code TEXT (3) NOT NULL" & _
" REFERENCES Services (service_code)," & _
" service_date DATETIME NOT NULL," & _
" service_mileage LONG NOT NULL," & _
"PRIMARY KEY (vin, service_code,service_date));"

End With
End Sub

Query: Vehicles Within Service Window

SELECT Vehicles.vin,
VehicleMileage.mileage,
VehicleMileage.reading_date,
VehicleServices.service_code,
VehicleServices.service_date,
VehicleServices.service_mileage,
Services.service_interval_miles,
Services.service_interval_months
FROM (Vehicles
INNER JOIN VehicleMileage
ON Vehicles.vin = VehicleMileage.vin)
INNER JOIN (Services
INNER JOIN VehicleServices
ON Services.service_code =
VehicleServices.service_code)
ON Vehicles.vin = VehicleServices.vin
WHERE (VehicleMileage.mileage > VehicleServices.service_mileage +
Services.service_interval_miles - 200)
OR DATEDIFF
("m",VehicleServices.service_date,VehicleMileage.reading_date) >=
Services.service_interval_months;

Warning: Completely untested.

 

[Guest]

I believe that Dorian's way just rack when due but does not keep history.

 

[Michael Gramelspacher]

I believe that Dorian's way just rack when due but does not keep history.

Karl, I think my query is wacko. I may have a solution, but it uses three
queries and is not pretty.

 

[Guest]

I tried that using the names I had already had in for tables and it did work.
Only thing is it will pull up every past record. If I have a history, say
Car 1 had service at 20,000, 23,000, and the last mileage was 23,500, it will
pull up up the 20,000 and 23,000 as being past due. They are past due, but I
really need to limit it to the last entry of each service any thoughts on
that other than using a Select MAX.

 

[Michael Gramelspacher]

I tried that using the names I had already had in for tables and it did work.
Only thing is it will pull up every past record. If I have a history, say
Car 1 had service at 20,000, 23,000, and the last mileage was 23,500, it will
pull up up the 20,000 and 23,000 as being past due. They are past due, but I
really need to limit it to the last entry of each service any thoughts on
that other than using a Select MAX.

Referring back in the thred to my post, this is what I came up with. I
cannot say that it is tested, because I have no data to work with.
(watch for line wrapping).

Query: TopMileageDate
-----------------------
SELECT VehicleMileage.vin,
MAX(VehicleMileage.mileage) AS current_mileage,
MAX(VehicleMileage.reading_date) AS [current_date]
FROM VehicleMileage
GROUP BY VehicleMileage.vin;

Query: LastVehicleService
--------------------------
SELECT a.vin,
a.service_code,
a.service_date,
a.service_mileage
FROM VehicleServices AS a
INNER JOIN VehicleServices AS b
ON (a.vin = b.vin)
AND (a.service_code = b.service_code)
AND (a.service_date <= b.service_date)
GROUP BY a.vin,a.service_code,a.service_date,a.service_mileage
HAVING 1 >= COUNT(* );

Query: VehicleServiceDue
-------------------------
SELECT Vehicles.vin,
LastVehicleService.service_mileage,
TopMileageDate.current_mileage,
(TopMileageDate.current_mileage -
LastVehicleService.service_mileage) AS miles_driven,
LastVehicleService.service_date,
TopMileageDate.current_date,
DATEDIFF
("m",LastVehicleService.service_date,TopMileageDate.current_date) AS
Months_diff,
Services.service_interval_miles,
Services.service_interval_months
FROM Services
INNER JOIN ((Vehicles
INNER JOIN TopMileageDate
ON Vehicles.vin = TopMileageDate.vin)
INNER JOIN LastVehicleService
ON Vehicles.vin = LastVehicleService.vin)
ON Services.service_code = LastVehicleService.service_code
WHERE (((TopMileageDate.current_mileage) >
LastVehicleService.service_mileage + Services.service_interval_miles -
200))
OR (((DATEDIFF
("m",LastVehicleService.service_date,TopMileageDate.current_date)) >=
Services.service_interval_months));

 

[Guest]

This appears to work. I ran some very small test samples through it and as
far as I can tell, it's catching everything how it should. I think the only
thing I added was for it to pull in the service name and the license plate
number since it's easier to identify which vehicle we're looking at by that
than trying to figure out what VIN belongs to what. Thanks

I tried that using the names I had already had in for tables and it did work.
Only thing is it will pull up every past record. If I have a history, say
Car 1 had service at 20,000, 23,000, and the last mileage was 23,500, it will
pull up up the 20,000 and 23,000 as being past due. They are past due, but I
really need to limit it to the last entry of each service any thoughts on
that other than using a Select MAX.

Referring back in the thred to my post, this is what I came up with. I
cannot say that it is tested, because I have no data to work with.
(watch for line wrapping).

Query: TopMileageDate
-----------------------
SELECT VehicleMileage.vin,
MAX(VehicleMileage.mileage) AS current_mileage,
MAX(VehicleMileage.reading_date) AS [current_date]
FROM VehicleMileage
GROUP BY VehicleMileage.vin;

Query: LastVehicleService
--------------------------
SELECT a.vin,
a.service_code,
a.service_date,
a.service_mileage
FROM VehicleServices AS a
INNER JOIN VehicleServices AS b
ON (a.vin = b.vin)
AND (a.service_code = b.service_code)
AND (a.service_date <= b.service_date)
GROUP BY a.vin,a.service_code,a.service_date,a.service_mileage
HAVING 1 >= COUNT(* );

Query: VehicleServiceDue
-------------------------
SELECT Vehicles.vin,
LastVehicleService.service_mileage,
TopMileageDate.current_mileage,
(TopMileageDate.current_mileage -
LastVehicleService.service_mileage) AS miles_driven,
LastVehicleService.service_date,
TopMileageDate.current_date,
DATEDIFF
("m",LastVehicleService.service_date,TopMileageDate.current_date) AS
Months_diff,
Services.service_interval_miles,
Services.service_interval_months
FROM Services
INNER JOIN ((Vehicles
INNER JOIN TopMileageDate
ON Vehicles.vin = TopMileageDate.vin)
INNER JOIN LastVehicleService
ON Vehicles.vin = LastVehicleService.vin)
ON Services.service_code = LastVehicleService.service_code
WHERE (((TopMileageDate.current_mileage) >
LastVehicleService.service_mileage + Services.service_interval_miles -
200))
OR (((DATEDIFF
("m",LastVehicleService.service_date,TopMileageDate.current_date)) >=
Services.service_interval_months));

 

[maria yolanda]

This appears to work. I ran some very small test samples through it and as
far as I can tell, it's catching everything how it should. I think the only
thing I added was for it to pull in the service name and the license plate
number since it's easier to identify which vehicle we're looking at by that
than trying to figure out what VIN belongs to what. Thanks

I tried that using the names I had already had in for tables and it did
work.
Only thing is it will pull up every past record. If I have a history, say
Car 1 had service at 20,000, 23,000, and the last mileage was 23,500, it
will
pull up up the 20,000 and 23,000 as being past due. They are past due, but
I
really need to limit it to the last entry of each service any thoughts on
that other than using a Select MAX.

:

I'm trying to figure out the best way to go about creating something to
track
when vehicles are due for their next maintenance.

I've created the tblVehciles, with the VIN, License, Make, etc, and the
tblMaintenance, which will track the DateofService, Mileage,
ServiceCompleted. Under ServiceCompleted I have a list to choose from, Oil
Change, Tire rotation, Oil/Tire Rotation, Mileage (just a mileage entry),
and
Other.

What I want to happen is to create an autoexe Macro that will run every
Monday to pull up a report that will show if any vehicle is coming due for
a
change. I know how to do the Macro, just trying to figure out the best way
to construct the query. There will be at least one entry per month for
vehicle where mileage is entered since those are turned in monthly, so that
entry would have Mileage under the ServiceCompleted field. I need to
somehow
query to find the last entry with Oil Change, and compare that entry's
mileage to the last entry's mileage. I also need to check that entry's
DateofService to the current date to make sure it hasn't gone past 6
months.
I'd really like to have it warn if it's within 200 miles of going 3000
miles
since the last change or within one month of going 6 months since the last
change.

I'd like to do the same thing for Tire Rotations, but that would just be
every 6,000 miles. Any ideas would be appreciated.

Referring back in the thred to my post, this is what I came up with. I
cannot say that it is tested, because I have no data to work with.
(watch for line wrapping).

Query: TopMileageDate
-----------------------
SELECT VehicleMileage.vin,
MAX(VehicleMileage.mileage) AS current_mileage,
MAX(VehicleMileage.reading_date) AS [current_date]
FROM VehicleMileage
GROUP BY VehicleMileage.vin;

Query: LastVehicleService
--------------------------
SELECT a.vin,
a.service_code,
a.service_date,
a.service_mileage
FROM VehicleServices AS a
INNER JOIN VehicleServices AS b
ON (a.vin = b.vin)
AND (a.service_code = b.service_code)
AND (a.service_date <= b.service_date)
GROUP BY a.vin,a.service_code,a.service_date,a.service_mileage
HAVING 1 >= COUNT(* );

Query: VehicleServiceDue
-------------------------
SELECT Vehicles.vin,
LastVehicleService.service_mileage,
TopMileageDate.current_mileage,
(TopMileageDate.current_mileage -
LastVehicleService.service_mileage) AS miles_driven,
LastVehicleService.service_date,
TopMileageDate.current_date,
DATEDIFF
("m",LastVehicleService.service_date,TopMileageDate.current_date) AS
Months_diff,
Services.service_interval_miles,
Services.service_interval_months
FROM Services
INNER JOIN ((Vehicles
INNER JOIN TopMileageDate
ON Vehicles.vin = TopMileageDate.vin)
INNER JOIN LastVehicleService
ON Vehicles.vin = LastVehicleService.vin)
ON Services.service_code = LastVehicleService.service_code
WHERE (((TopMileageDate.current_mileage) >
LastVehicleService.service_mileage + Services.service_interval_miles -
200))
OR (((DATEDIFF
("m",LastVehicleService.service_date,TopMileageDate.current_date)) >=
Services.service_interval_months));