Query to forecast the vehicle service due date

[dreamzdestiny]

Hello,
I'm totally new to this forum. I have a database with the vehicle information in one table. The Vehicles' Odometer reading stored weekly in another table. I have the vehicles' service history with the Odometer reading of the last service in another table. The first two tables are linked by parent, child relationship. I now want to forecast the approximate vehicle service due date based on this information. All I want to do is extract the last 10 Odometer Readings from the Weekly Odometer Readings Table. Find the Average Kilometers with those 10 values. Come up with a figure. Determine the last value entered in the Weekly Odometer reading along with the date. Pull the last service history for the vehicle, with the odometer reading and date. Compare these 2 readings to see if the vehicle is due for service, and if so based on the average Kilometers run every week, determine the approximate date the vehicle is service for due. I badly need help on this one. So if someone could help me on this please let me know.
Thanks

 

[RuralGuy]

You will get better assistance if you will break down your problem into sections and let us know what you have so far.

 

[dreamzdestiny]

I have a database with the following tables:
Vehicle Info - Which Stores all the vehicle Master information (Hardly does have any changes to it - In most cases only when a new car is bought by the company)

ID Manufacturer Model Year Steering
10000 Nissan Maxima 1979 Power
10001 Holden Commodore 1985 Power
10002 Toyota Corolla 1988 Power​

Weekly Odometer Reading - The Vehicle's weekly Odometer Reading stored in this table. (I've created forms to update data into this table)

ID Date Weekly Reading
10000 24/03/2006 15646
10000 31/03/2006 16788
10001 24/03/2006 26454
10001 31/03/2006 28500​

Service Log: - To store the vehicle's service history (I've created forms to update data into this table)

Job Number ID Date Current Odometer Remark
2 10000 30/05/2006 48995 xxxxxxxxxxxxxxx​

I've created queries to rerieve the last 10 weekly entries per vehicle ID based on user input and made that a make table query. The table has the Vehicle ID, Date & Odometer readings.
I've created a query to rerieve the last record for the corresponding vehicle ID from user input to see their service history which has the date and Odometer reading
I now need to design a form where the user enters the vehicle ID, and it tells if the vehicle is due for service, and if so an approximate date on when the vehicle is due for service.
With the Last 10 entries from weekly entries table, i need to determine the average kilometers run each week.
I also have another query to determine the last entry in weekly odometer reading as well.
So I need to find the difference b/w Last Odometer Reading - Weekly Entry & Last Reading - Service Log. If the difference is lesser than set parameter (say 10,000 kms), then based on the average kms calculated with the last 10 entries, let the user know when the vehicle is next due for service.
It need not be an exact date. It could be an approximate week. I have designed the db till this, but i'm not sure how to proceed from there. Your help on this would be much appreciated.
Thanks

 

[RuralGuy]

Let's hope a query guru will drop by.