Eric Davis
asked on
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.
https://stackoverflow.com/questions/33311884/join-two-tables-on-date-where-date-from-right-table-is-greater-than-date-from-l
Please excuse my communication skills. I am not a data professional but these sorts of problems are a large part of my job.
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.
https://stackoverflow.com/questions/33311884/join-two-tables-on-date-where-date-from-right-table-is-greater-than-date-from-l
Please excuse my communication skills. I am not a data professional but these sorts of problems are a large part of my job.
ASKER
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.
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.
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)
FROM (
SELECT * FROM tblAlarmStatus
WHERE SUBJECT = "ALARM"
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 .
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)
FROM (
SELECT * FROM tblAlarmStatus
WHERE SUBJECT = "ALARM"
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 .
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:
UnitID
Received
Subject
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.
So the sort is:
UnitID
Received
Subject
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How are you handling the non-matches?
ASKER
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 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.