Query Service History/Future Dates

[Coyote_Jackson]

Trying to setup a query which takes into account a list of cars using reg
numbers:

1 ----
2 ----
3 ----
4 ----
5------

up to 100+ cars

each car will need to have a service within 3 months for the next 3 years.

I'm sure the services for each car will be staggered depending on their
previous service histories.

I need a query that i can possibly run daily showing that carA needs to e.g
have a service in 21 days... or 7 days... also this needs to take into
account a possibility there could be extrernal delays e.g. "driver off sick
to take car in"

I have thought about using Max(date) in someway or the other but keep ending
up withj loose ends...

 

[KARL DEWEY]

Here are two post of mine on maintenace --
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.
----------------------- ---------------
In a Task table have a field indicating interval number for the maintenance.
Use the lowest common denominator such as weeks, months or quarters.
If you can not make it work with the lowest common denominator then use two
fields, one for interval type and other for numerial --
m 2 - for 2 months
d 30 - for 30 days
q 2 - for 2 quarters
Use these in DateAdd function to create your workorders using an append query.

Another thing to think about is whether to schedule based on last performed
date or straight calendar. If a maintenance task was performed late or
earlier should the next one be be form the completion date or whenever the
calendar says it should be. Have a field in the task table indicating which
if you have mixed.
The workorder needs a date field for DueDate and Completed. The append
query will look at task table for interval information and which date to use
- last completed or last scheduled.