Tables :: Auto Calculating Field In Table

Currently I have a form with these variables

- Assets
- Shifts
- Machine Offline Date
- Machine Offline Time
- Machine Online Date
- Machine Online Time

In the Asset Table I have recorded on each asset the number of shifts it operates for as well as starting / finishing time of those shift. e.g. 2 shift asset will start at 7:00AM finish at 11:00PM
or 3 shifts asset will start at 7:00AM finish at 6:59AM

Now I have 82 assets in the factory and 20% of those machines run 3 shifts. Each shift is 8 hrs.

What I have already done is allocate shifts per asset e.g.

when I pick Asset (a) in the Asset combo box, in the shift box it will automatically generate 2 or 3 dependant on what I have set.

If an asset runs for 2 shifts, it would mean that, that asset is operational/running from 0700 - 2300 or 7:00am - 11:PM

also if an asset runs for 3 shifts it would mean that, that asset is operational/running from 0700 - 0700 or 7:00AM - 7:00AM

These are the scenarios that is entered through a "Form"

Scenario A: Machine (a) breaks down at 1700/5:00PM on the 10/7/15 and was back online at 12:30 on 11/7/15, This machine runs for 3 shifts which would mean in the "Breakdown Downtime" the result should be 19.5 hrs

Scenario B: Machine (b) breaks down at 1900/7:00PM on the 10/7/15 and was back online at 10:00AM on 12/7/15, this machine runs for 2 shifts which would mean in the "Breakdown Downtime" the result would be 23 hrs.

I would like to make this an automatic calculation, Is this possible?

Please note I am not after a "Query", I need this calculation in the main Database table named "Tbl_MaintenanceDATA".

I understand this is not the norm and many have suggested against this, however for the purpose of what I need, it needs to be in the main table with all the other data.

Here are some snip it
snipit2.png
snipit.png