Access time results for asset events that exclude non chronological records

I have an access table linked to an outlook email folder that is shutdown and restart events associated with a machine [Skid]. The events are time/date when the machine is shutdown [Alarm Received] and when the machines are started [Alarm Cleared Received]. I need to get a time/date field from query to relate to a time date in another query where the time is in order and I can calculate the difference.

Current results:

The [Skid] number is shared between the two queries. I cant figure out how to get rid of results like the middle record where it uses the first time received and the last time received. I essentially want a time total of how long the machine is off on each cycle that is shuts down.  

Desired results:

The two queries that this one joins were the result of splitting the events from the email folder into Alarm Received events and Alarm Cleared Received events. I am sure this is the wrong way to do this but my I don't have programming skills and am attempting to accomplish it with the query builder.

Current query:

I think this is telling me how to do it but its a little confusing to me.

Please excuse my communication skills. I am not a data professional but these sorts of problems are a large part of my job.
Eric DavisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What does the input file look like?  Are you processing the emails in real time or as a batch?

It seems to me that "Alarm Received" should insert a new row and "Alarm Cleared" should find the most recent row with a null value in Alarm Cleared and update it.
Eric DavisAuthor Commented:
Here is the linked outlook table as the email is received.

I use the highlighted fields. Subject = Alarm Received or Alarm Cleared. Received = time/date of alarm. Contents = string that contains location and machine ID.

I will process the alarms daily in a 24 hour batch.

Here is the query results after I have extracted the machine ID from the Contents field via InString query. This is what feeds the query that produces the set of results from my first post.


Thank you for taking the time to understand my problem.
Dale FyeOwner, Developing Solutions LLCCommented:
personally, I think I would be putting these all into an "AlarmStatus" table with fields
Subject, Received, Station, and Skid fields.

Then, assuming that you will not get another "Alarm" message from a particular station/skid combination before receiving a subsequent "Alarm Cleared" message, you should be able to do something like:

1.  Create a query that for each alarm, identifies the time of the next Alarm clear

SELECT A.Station, A.Skid, A.received as StartTime, Min(AC.Received as EndTime)
SELECT * FROM tblAlarmStatus
AND Received >= DateAdd("d", -1, now())
) as A LEFT JOIN (
SELECT * FROM tblAlarmStatus
WHERE SUBJECT = "Alarm Cleared"
AND Received >= DateAdd("d", -1, now())
) as AC ON A.Station = AC.Station AND NZ(A.Skid, "") = NZ(AC.Skid, "")
GROUP BY A.Station, A.Skid, A.Received

From here you could create another query to computes the difference between the Start and End times of a particular alarm.  Or you might even be able to add:

DateDiff("n", A.received, Min(AC.Received)) as ElapsedMinutes

to the end of the SELECT clause above.  Although I would probably elect to take the query shown above and use that as the source of the final query .
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

I wouldn't do this with SQL if you're doing it as a batch.  There are too many situations to consider and you need to be able to both add new records and update existing records.   I would create a query that sorts the data by whatever identifies the object we are talking about.  There should be some unique ID for each unit but I can't tell what that field is by looking at your example.
So the sort is:


You would then create a code loop that reads each record and formats a new record for adding when the subject is "Alert" but waits until it gets the next record before writing it so it can pick up the cleared time.

The logic needs to handle alarms without clears and clears without alarms.  Given that you have to start at some point there might be clears that belong to alarms that arrived prior to the current set of records.  Your logic would need to look in the existing table to see if those could be identified and update them.  For Alarms that have no clears, that is fine, you would just write the records and presumably, the next batch will contain the clear records.

If you were doing this "live" so that you are always only processing one record at a time, then a query would be the best solution.
Eric DavisAuthor Commented:

First, thanks for the suggestions from you both. My biggest problem when problem solving these things is that I'm self taught and therefore lake the understanding of basic best practices for data. I also have not successfully done any coding. This forces me to lean on the query and expression builders and Google for my Access endeavors.

I was able to solve the issue (I think) with queries. First I put an event id field in a table and populate that table via append query of the alarm event list sorted by alarm time. Then I created queries to split the list into alarm and alarm cleared results. Then I combined the alarm events on the skid number and combined the alarm id and alarm cleared id into the same field and did a date diff calculation between the alarm times. This gave me the results like I showed in the first post called "current results" but with a unique id based on the alarm and alarm cleared id. Then I created another query to with grouping set to Min in the minutes field and the combined event id field set to First. The results were the combined ID that I related to the current results. I manually compared a sample of about 50 results to these filtered results and it appears to be working. I can post some snips of the queries if anyone is interested.

My next problem is how to resolve an asset/resource table that changes when the machines are moved from location to location or removed/added. I think I need to create a list of all machines that we've ever operated and date stamp the install and removal events but I have a feeling I'm missing something.

Thanks again for the advice

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
How are you handling the non-matches?
Eric DavisAuthor Commented:
For non matches (which are due to removed skid numbers) I will update my asset table with additional fields for an event associated with that entry such as "decommission" or "commission" and date. Then anywhere that I reference the table ill have to insert a query to reference the event and date. I think. Is that the non-matches you were referring to?
Non matches are records that have an end time but not a start time.  They are records that were started before the cutoff for the data extraction and not yet completed.  You find the other situation at the other end.  Records that are started but won't finish until tomorrow or later.  Since the query deals only with records in the recordset, you will always have these incomplete situations unless you use the method I suggested.  You will see them eventually.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.