Auto Calculating field in table

[iLott]

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 - 11M

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.:banghead:


Here are some snip it

 

[arnelgp]

where do you record this breakdown time? is it on the subform Maintenance Task Detail?

 

[The_Doc_Man]

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

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

I see what you have said and don't care that you aren't looking for a query. It only tells me that you have fixated on something someone suggested, or have been given an instruction by someone who doesn't know what they are doing. Don't think table OR query until you are sure your design has enough data to track what you want to track. A table and a query can be used for almost exactly the same purpose about 99%+ of the time. I see nothing to suggest that the scenario you described falls into that <1% category. However, on the odd assumption that you have a good reason for asking this, you already have the answer at hand provided your user interface is set up correctly.

1. The users must NEVER see the navigation pane - they must ALWAYS and ONLY see forms, perhaps including a dispatch / switchboard form. This prevents them from trying to manually enter a downtime event into your downtime table. The moment this can occur, you cannot trust the contents of ANY record in that table.

2. On the form where your machine downtime information is noted, you will probably need to do some VBA computations behind the scenes. But the TABLE does not do this. The FORM does it. You DO NOT want to try to make this a table-centric computation. You want it to be FORM-centric, because there is where the action occurs. Tables, to be safe repositories of data, must be inviolate when not actually up on-screen via opening the table, a related query, or a related form.

3. Your design needs to be very precise here. From the description of your goal and by inference from your pictures, I see not less than two tables - one for the device / asset and one for the outage. Your .PNG files show that you will have some limited amount of ASSET data but you are entering OUTAGE data that can (theoretically) be unlimited. I.e. a balky asset can have as many outages as really happened until you get tired of the asset and replace it. In that sense, no limit. In the sense of your patience with it, perhaps more tightly limited.

OK, so how would your form work? (Rhetorical, of course...) On the form, you select the asset, some qualifying information, and you have two important aspects that must be used for the computation. Stay with me here... you have a date/time it went down and a date/time when the technician put it back in service. All the rest of that stuff is window dressing and descriptive data.

Your REAL down-time for any asset is merely the difference between the time it went out of service and the time it was returned to service. Your EFFECTIVE down-time computes the effect of shift stop/start times on the REAL down time. So the moment you have a date/time for return to service and a date/time for out-of-service, you can begin the computation you wanted IN THE FORM. Where you do it depends on a lot of factors, but given your example which flows top-to-bottom, I would say that in the LostFocus event routine associated with the return to service, you could write your computation.

OK, so how might you do this? Well, I tend to use Date variables to compute date/time values (I'm kinky that way). So in working copies, I would set up two or three variables as appropriate for my style of computing. I would have a zero EFFECTIVE downtime and a running date/time slot.

Now, I start looking a the shift start/stop times. The question you must ask is whether the restoration to service is in the current shift or NOT in the current shift. If the return to service is NOT in the current shift, you take the running time variable and compute the time difference to end-of-shift. If the current shift is one in which the asset would have been used, add that difference to the EFFECTIVE down time. Now step to the next shift and repeat the process until you find the shift in which return-to-service occurred.

In that shift, the difference between START of shift and the return time is added to the EFFECTIVE down time unless that shift was not one in which the asset was normally active. Now at that point, the running EFFECTIVE downtime total IS your outage total.

In the little loop I described above, you have three points of interest. The first iteration adds the time from the downtime event to the end of the shift. The last iteration adds the time from the start of the shift to the restored-to-service event. In between, you add the length of the shift. In all three cases, that addition occurs only if the asset was supposed to be running during the shift.

Now, the payoff question - how long will this take? In the form, if this is implemented in VBA code, it will potentially be too fast to notice, because VBA on modern Windows boxes is very fast.

If you do all of this in the form, then you have everything you need on-hand (inside the form) at the time. If you tried to make this a table-centric computation (OR a query-centric computation) would probably eat your lunch for you because of the complexity of the computation.

The only wrinkles left are the details of how you store times and dates for your loop and the exact format you want to see when building reports. You can search this forum for probably a gazillion entries on date formatting and manipulation, or you can work your way through this food for thought and ask specific questions separately once you have something specific to ask.

 

[iLott]

First of all

Thank you for taking your time to respond.

Second you are correct in the matter that, I have been instructed by my superiors who doesn't understand or have the basic knowledge in how MS access operates.

Asides form the snip its I have shown you I have approximately 12 tables all with specific data set which keys in to the Mother table or a "Data warehouse table" if you want to call it that everything is inter linked between the tables and forms.

In this data warehouse I have currently 2-3 Auto calculation set columns, dependant on what is entered in the "Form" by the users. So an answer to your number 1 question is, I am the only one who has administrative permissions, thus no1 is able to manually alter Downtime themselves outside of the form.

these auto calculations are in table basic expression calculations

example: Man hrs = planned downtime * number of technician
Costs of downtime = Man hrs * (x) where x is hourly rate.
This is the reason is why I was hoping that I would be able to make an auto calculation box regarding the current topic as well.

 

[The_Doc_Man]

Let me try to make this point again. You CAN make an auto-calculation box. You just can't do it in a table, particularly given the complexity of the shift-outage effect. (Or, to be pedantically precise, nobody would WANT to write the complex logic needed for that function in a table.)

Do it in a form where you can write darned near any code you need to write. You can compute ANYTHING in a form and can store what you computed (via either Recordset or SQL "INSERT INTO") practically anywhere you can store it. If you entered data for table 1 and it impinges on table 2, you can do multiple sets of computations behind the scenes and can then do multiple updates of multiple tables behind the scenes.

It is seriously difficult to efficiently implement computational logic (such as the effect of variable shift activities on an outage) in table-resident code even in an ORACLE database, where a data trigger is possible. If you are doing this in Access, trying to encode these rules in the table's default value code or in some table event is terribly difficult, particularly when compared to doing it behind a form.

Let me add this thought: If your superiors don't understand Access well enough to specify the "right" way to do it in the first place then they will never be able to understand that you did it the right way and that it just wasn't the way they requested. So treat their suggestions the same way as Captain Jack Sparrow treats the Pirate's Code. It's just a guideline!

 

[iLott]

Yes, I shall do my calculations in the form...

I can get the date/time difference easy enough how do I incorporate the 2 or 3 shift variable within the calculations?

And how would you recommend doing the formula?