Code to multiply a number

[shariq1989]

When an new overhaul record is entered for a component (ComponentID), I want the overhaul interval (OvhlInterval) for that component to double automatically, so that the application can keep repeating.

Tables:
TblCompletedOverhauls
OvhlID (Pk), ComponentID, DateOvhled

TblComponents
ComponentID, ComponentName, OvhlInterval

What is the best way of doing this?

 

[AUGuy]

Maybe it's just me, but I'm not very clear on what you're looking for. Could you go into further detail on what it is you're doing as well as how the information gets into the table, etc?

 

[pbaldy]

I would think the interval would remain fixed (x number of miles, hours, etc). In car maintenance applications I store an interval for various items (like oil change). I have a table with maintenance records, where I can find the last time a vehicle had an oil change. I have a table with the current odometer. I can compare the last service and the interval against the current odometer to determine when the vehicle is due for service again.

To do what you're asking wouldn't be difficult. You could run an update query using the ID as a criteria, and double the value. However, I suspect it would only double the first time. In other words, if my oil change interval is 5k miles and I used your method, I'd double it to 10k. However, when it was serviced at 10k I wouldn't want to double it, as that would make it 20k and I want it done every 5k. So, I don't really think your doubling is the way to go.

 

[shariq1989]

@AUGuy: Maybe it's just me, but I'm not very clear on what you're looking for. Could you go into further detail on what it is you're doing as well as how the information gets into the table, etc?

I have a form called Hour Entry which vessels will use to enter monthly engine hours through and it feeds tblHourEntry. I have TblComponents which contains a list of components which are associated with different machinery. Each component has an overhaul interval. I hope to create a query that uses MaxofHourEntry (Latest hour entry record for that machine) and the OverhaulInterval to find the number of hours to go for the overhaul. I want this application to last on its own. Example: Pistons will be overhauled every 35000 hours. What happens after the first overhaul? How is this best calculated?

After that, I want to set up alerts so that one month to go till inspection is code red, three months to go is code yellow. I want to have the application take an average of the last six monthly hour entries and then use it to turn the "hours to go till next overhaul" to "time to go till next overhaul" so that these deadlines can fall in with the rest of my calendar deadlines.

@pbaldy: You are awesome. That was pretty dumb of me to try. Especially after having gone through two years of calculus... I guess time to go=interval hrs - (hrs at end of month - hrs at last overhaul) would be the best way to go?
35,000 - (38,000 - 35,000) = 32,000.

 

[pbaldy]

Probably several ways to run the math. I use

[MaxOfMileage]+[RegServiceInterval]-[Odometer]

which would yield the same result

35,000+35,000-38,000 = 32,000

In my case the odometer readings in my vehicles table are updated via other processes, so basically they're updated every time a vehicle is used (limos, buses and taxis). I created "due for service" reports where a vehicle won't show up until it's within "x" miles of being due. Of course, they wanted different thresholds for different services, so it's a little convoluted (oil changes show up when within 1k miles, transmissions when within 3k miles type of thing).

 

[shariq1989]

That is more or less exactly what I am doing..