Query for scheduled service

[chris89]

Hello everyone !
I'm trying to figure out how to design a service scheduler based on the date a Product was purchased and date that the Product should be serviced based on Manufacturer recommendations.
My problem is that I can calculate the date difference between datePurchased and dateTheManufacturerRecommends but how can I do this for example every 6 months for a specified type of maintenance??
Any help would be much appreciated !
Thank you in advance

 

[pbaldy]

I would probably have a maintenance table to record each time the item was serviced. You then calculate your 6 months off the last time it was serviced. With proper setup, you can have multiple items and types of service accounted for.

 

[chris89]

That is what I thought but the proble is the purchased date.
For example when the product is purchased the recommended service date would be : RecServDate= DatePurchased +6 months .But when a product has already been serviced this should be :RecServDate=LastServDate+6months .

So I thought of something like this :

if (DatePurchased !=0) //if datePurchased is not null , in case that the product has not been purchased yet
{
RecServDate=PurchaseDate+6months
}

else { RecServDate= LastServDate+6 months
}

The problem is that I 'm very new to Access so I don't know how to express the above code.
I think with iif() but not very sure!
Any suggestions?


Btw thank you for your response

 

[pbaldy]

I've written car maintenance applications and what I did was to add a "seed" record to the maintenance table when a vehicle was purchased at whatever it's mileage was at that point. You could also use the Nz() or a custom function so that if no maintenance record was found the purchase date was used.

 

[chris89]

So how did you use "seed"?

 

[pbaldy]

I had SQL Server so I used an insert trigger to add records to the maintenance table whenever a vehicle is added to the vehicles table. With an Access back end you would code it yourself, in the form users added records with. You could use a recordset or fire off an append query.

 

[chris89]

I see!
The thing is that I will enter the data for the recommended maintenance by myself so there will be no use for an append.
Could you please advice me how to code the above algorithm in Access?

 

[pbaldy]

You could use something like this to get the most recent service:

http://www.baldyweb.com/LastValue.htm

If you LEFT JOIN that to the table containing your products and the purchase date, you should be able to use the Nz() function to return the most recent service or the purchase date if there is no service.

 

[chris89]

So for example if you had a car database would you have a table like this:

tblcars:
.carId
.partId
.purchasedDate
.maintenanceDate

Thank you for your responses !

 

[jdraw]

There are some free data models here that may be helpful
http://www.databaseanswers.org/data_models/index.htm

and design info here http://www.rogersaccesslibrary.com/forum/topic238.html

 

[pbaldy]

No, I have

tblCars
.CarID
.PurchasedDate
.Odometer

tblServiceCodes
.ServiceCode (oil & filter, brakes, etc
.Description

tblServiceIntervals
.ServiceCode
.ServiceInterval

tblService
.CarID
.ServiceCode
.ServiceDate
.Odometer

In my case the odometer field in the cars table is updated to current by other activity, so it always contains the current mileage, which I can compare to the last service and using the intervals table determine when it is next due.

Gotta go handle a server problem, sorry to be brief.

 

[chris89]

I appreciate your help thank you!