Auto Calculating Downtime with different variables

[iPanterra]

Hi

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

This is the scenario

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? If so how do I need to write a VBA or is there a much simpler way?

[Rabbit]

I don't know your table structure but if you have the date time that it went offline, the date time that it came back online, and the start and end date time of the shifts, then what you can do is join the shifts to the offline online data and return all those where the date time ranges overlap. Then you can use the DateDiff() function to calculate the number of minutes between the two date time ranges.

[iPanterra]

This is a snip it of my table, unfortunately I couldn't paste a print screen image so I typed it out.

Tbl_Department_Asset

Asset / Effected Area / Shift / Start / Finish

Sand Mixer/ Foundry/ 2 / 7:00AM /11:00PM
Heat Treat Oven / Stainless/ 2 / 7:00AM / 11:00PM
Centre Lathe / Machine Shop/ 3 / 7:00AM / 6:59AM

[Rabbit]

What the table structures you have posted, you should be able to implement what I laid out in post #2.

[iPanterra]

Can this all be done in 1 box? or do I require multiple boxes for the 2-3 shift variable?

[Rabbit]

I don't know what you mean by "1 box". Box for what?

Your shifts are in separate rows in a table, not in boxes. The algorithm I laid out in post #2 is how to create a query to return the downtime numbers you described in the first post.

[iPanterra]

I need the calculation to be in my main "database table" and not just in the query form. Therefore I need a function that will do these calculation in the table itself.

[Rabbit]

It's usually a bad idea to store calculations in the table itself. It's better to have it in a query so that the calculation will always be up to date. When you store it in the table itself, you have to worry about it being out of sync.

[iPanterra]

I understand that, and that's what I explained to my superiors regarding up to date, "data" however they want it as a 1 point information with all present and historical information, thus why they want the full table.

[Rabbit]

In that case, you still do what I laid out in post #2, except change it from select to an update query.