Timed Event Database

[Tieval]

Hi,

I am at the planning stage and am looking at a piece of software that outputs an activity log of various functions. For example:

08/09/2015 09:00:00 Switched On
08/09/2015 09:12:26 Started Test Cycle
08/09/2015 09:25:12 Completed Test Cycle
08/09/2015 09:32:00 Started Production
09/09/2015 01:24:00 Completed Production
09/09/2015 01:31:26 Switched Off

I would like to write an accompanying database for stats which for example would show days, hours, minutes, seconds switched on or running test cycles or in production on set days, weeks, months or years.

I have a degree of control over the input data and no problem importing it into access and ensuring that the data is ordered correctly but am not sure where to start with evaluating data.

Any clues on where to start which would save me going a long way and then hitting a dead end?

 

[Ranman256]

in a form, you show a list of events possible,
user dbl-clicks the event he is doing, it posts the event-time to the log.

vUser = Environ("Username")
sSql = "insert into tLogs ([Machine],[event],[timestamp],[user]) values ('" & forms!frmMain!cboMachine & "','" & forms!frmMain!lstEvents & "',#" & now() & "#,'" & vUser & "')"
docmd.runsql sSql

 

[TimW]

Hi
Just quickly, i think you will need.
To identify the function you are evaluating (column for that). The rest is just maths. You need to identify the time the machine is on.
Total on = Elapsed time - (time off1 - time on1)+(time off2 - time on 2) etc
test time = Test ended 1 - test started 1 + test ended 2 - test started 2

You will need to work out how to loop through the data to get the information you need (recordsets...)

If you need more information, can you supply more details and I can see if I can help

T

 

[The_Doc_Man]

Here is something that might be helpful.

You have a date and a time for your events. In Access, a date/time variable is actually a "typecast" (alternative viewpoint) of a DOUBLE variable, where the value of a date is just the number of days and fractions thereof between the calendar date and the program reference date, which is midnight of either 1-Jan-1900 or 31-Dec-1899. Excel uses one of those, Access uses the other, Windows itself matches one of those two. I have to admit I can never remember which date is used by which facility. But I digress....

To do a computation of how long something has been in a particular state, you could do this: Make a table of the devices. (I'm sure you already have this.) Then make a child table of device transitions from one state to the next. (I.e. device X was off and was switched on at this date/time.) This has a FK to your device record and a date field and a state field. Whether the state is itself a code from a table of legitimate states is up to you. This transitions table might have other operational data if needed, like names if you needed to know who changed the device state, or stuff like that.

I propose that you add one more field, a DOUBLE, to that table for your state-time accumulation. If you have a DOUBLE variable in the table of transitions, you can compute the time something was in state X by finding each record that shows something went INTO state X and then finding the next transition to see the time when it LEFT state X - and store the elapsed time into the "Entering X" record. (Or the LEAVING record, your choice.)

There are those who would say you should not store this difference since it is easily computable, but I'm being more of a pragmatist here. By storing the amount of time in state X for device Y, you can then treat this as an "Inventory" type of question by summing the elapsed time counters for each state for each device. Do an "ORDER BY" on device first, state next, and compute running sums across the device transitions. THAT is a trivial exercise compared to the continuous re-computation of state-times.

The last part of this is the display of the elapsed times, which are not really dates at this point (because of the typecast to DOUBLE). You can do a "FormatDateTime" function in which you give it an elapsed-time type of template rather than a named time format. Use "hhhh:nn:ss" on the reversed typecast: CDate(DOUBLE) is treated as a time, so the format will work on your individual or summed elapsed-time fields.

You could also do this: Take that raw double in a query where you multiply the field by 24 for display purposes, and show it with a fixed number of decimal places using a numeric Format$ call. That would give you the hours and fractions in a particular state if you want time that way.

In case you were wondering, your DOUBLE variable offers up enough bits for about 55 bits of precision in its mantissa. We are only about 42,300 days (give or take a couple) from the reference date, so the date part fits comfortably in 16 bits = 65536 days, and will continue to fit in 16 bits until about June of 2079.

Take out the 16 bits for the date from 55 bits in total. That leaves us with 39 bits of precision for fractions of a day. One day is 86400 seconds which fits into 17 bits (= 131,072 seconds.) That leaves us 22 bits for the mantissa, which is enough for storing time to the microsecond. (You need only 20 of those bits bits to store numbers from 0 to 999,999.)

Anyway, to store elapsed times to a second for a single device, we will need to look at numbers representing times to about 33 bits. A SINGLE isn't that precise - but a DOUBLE is. Further, there are enough bits left over in that DOUBLE to assure no significant round-off errors or loss of precision in the number of seconds. That ought to be good enough for tracking your state-times to the second.

 

[Lightwave]

I created a Race Timing database which tracked competitors through a race. It was specifically aimed at triathlons but I imagine the structure for your database could be exactly the same. Each competitor was given a RFID chip (Radio Frequency Identifier) and we would lay an antennae across the ground.

All competitors would get a start time. That was T1.
As a competitor came out of the water they would run across the matt into the transition area where they could collect their bike T2. The competitors would run back and forth across a number of matts which could be in any location as no competitor could run across the matts all at the same time. Each time they did this the boxes would emit a string consisting of the ID of the chip and a time stamp.

Access would parse these strings into separate values of ID and time and match them to the competitors. We would be left with a series of times for each competitor
T1
T2
T3
T4
T5
T6
T7



Provided every competitor goes through the same process or you know before hand the order in which each process will be carried out (and you allocate that process to the competitor, by taking the later time from the earlier time you can get your times that you need for each segment.

So in setting it up I had three main tables

A Chip table - containing the hard coded ID of each chip

A Time table - that took the log from the antennae boxes - consisting of ID of chip and time code.

A competitor table - that took the list of entrants and allocated them chips

The clever bit was taking the Time table and filtering it by each competitor (presumably you will filter it by component) and then taking later times from earlier times. Each of these gaps you allocate to a process - In your case Start of Test Cycle / Production Cycle

You Pivot the Times and then work out the times between and show in a pivoted table and there you have it.

Be warned though my solution requires iterative calculations and I found that beyond 2,000 competitors and things started to SLOOOOOOWWWWWW down

This is where you want complex event processing - to calculate these gaps BEFORE they get into the database You could probably do something clever whereby you chunk up the information into Batches and only calculate information on the additional information - You will though have to ensure that new data in the new period does not relate to data in the old components This would be fine if you knew that components started and ended processes at different time. Its a nice problem that regularly comes up.