Query question

D

Dwayne B

Hi,I am a new user and have been using access 2003 for labout 6 months but I
am stumped on this problem, I have trucks numbered 1-10 with a service
interval that varies with the service type such as a 60k pm is done every
60,000 miles and a 40K pm is done every 40,000 and 20k is done every 20,000
miles .Each unit has a weekly mileage entered and when the mileage is within
the last pm mileage done + the pm interval it is due that pm.I do not have a
problem with these calcalations in separate querys for each pm telling me
what is due ,What I need is a query that will give me Only what is due now
based on the current mileage,since each pm has a different interval .I do not
know how to combine these in to one query ,any help would be much appreciated
 
K

KARL DEWEY

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 your case the lowest common denominator is 20K.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top