Filtering the impossible...

[Finchy]

Hello All,

Let me start by saying that I am new to access and also new to forums and the problem I am about to write about is difficult to explain but I will try my best..... Here goes.

OK I work with a database called the 'Online Archive' it works in Access '97. To help you understand what the database is used for I will quickly describe the function of my Job.

I work as a SCADA Operator (Supervisory Control and Data acquisitioner). I have several terminals running Win CC. This program monitors an automated warehouse, more specifically it monitors un-explained stoppages. If a stoppage occurs I get alerted in two ways. Firstly, graphically the equipment will go red and secondly I get an 'Alarm' which provides details about the stoppages (time/location/reason etc). There are 3 states to each alarm. Error occurred is the start time, error acknowledged is when I click to say that the alarm has been noted and appropriate response has been given. Alarm solved is the final state.

Going back to my database. The WinCC system mentioned above logs all this data and via an OBDC linked table exports all these alarms along with the details to the Online Archive. Its then my job to fill in reasons why these stoppages occurred. At the end of the day all the days errors are exported to an excel file and used in another database.

My Problem:

Each alarm has the following data:

Date/Time/MsgNr/Time/Diff/Status/Counter

The data I want to concentrate on here is contained within Time Diff, Status and counter. The other columns are irrelevant for this issue.

Time Diff For each alarm there will be 3 of these 1 for each status. The time diff for a status 1 alarm is always 0. The time diff for a status 2 alarm is the time difference between status 1 and status 2 of that alarm. The time diff for a status 3 alarm is the time difference between status 1 and status 3 of that Alarm.

Status is 1, 2 or 3. 1 = Alarm Occurred 2 = Alarm Acknowledged and 3 = Alarm solved.

Counter is a unique number generated by WinCC and cannot be changed

I Appreciate this may be a little hard to follow so here is an example of one alarm.

14/07/2010 00:06:05/51040/0/14646185/1
14/07/2010 00:07:00/51040/55/14646186/2
14/07/2010 00:07:08/51040/63/14646187/3

As you can see this error was solved in 63 seconds and acknowledged after 55 seconds.

Now, in the data that is used at the end of the day every alarm that never reached the 3 minute mark is not counted yet we still have to fill them in. There are a lot of these alarms that don’t reach the minimum time and I feel it is a waste of our time filling them in so I was looking at a query which could identify the time difference between the status 1 and status 3 alarm and 'autofill' the data accordingly but it is proving to be an impossible task due to the following.

The Online Archive has a Graphical form where we fill in the reasons for the errors, these alarms then get stored in another table with the reason ALWAYS assigned to the Status 1 part of the alarm. The time difference of status1 part of the alarm is always 0 since this is when the alarm occurred, which rules out a less than 3 minute type of query!

The counter never really has any sequence. Since several errors can occur at any one time and the counter goes up by 1 for every update to any alarm. So I cannot simply write a query which looks at the alarm by using the counters.

The query needs to do the following:

Look at the alarms, identify which status belongs to which alarm. The same alarm can only ever be active once at any one time but there is a possibility of it happening several times a day. By this I mean that error 51000 can only be active once at any one time but it could have been in error 15 times throughout that day, so the query would have to work this out. I think this bit maybe quite simple but I cannot for the life of me work it out.

The next bit is it needs to work out the time diff between status 1 and status 3 and if it is below 3 minutes (180 seconds) then It needs to assign the reason to the status 1 part of that alarm. It would also need to take into account that this error could have been active more than once and thus not alter the other ones.

It is worth noting that nothing can be changed about how the data comes into access. The WinCC project does not have that feature, I have looked!

I think for now I will leave it at that, chances are this makes no sense what-so-ever. I hope someone can help.

If any more details are needed then please ask and I will try my best.

Thanks very much

Finchy!

 

[jzwp22]

OK, if I understand you correctly, you need to find the corresponding status 3 record for each status 1 record. From there you need to determine if the timediff is <=180 seconds. If so, you want to update the "reason" to some prescribed text. The main question I had is where is the field that will hold the reason? Is it in the same table that holds the alarm data you provided (as shown below)?

14/07/2010 00:06:05/51040/0/14646185/1
14/07/2010 00:07:00/51040/55/14646186/2
14/07/2010 00:07:08/51040/63/14646187/3

I think the way to handle this is with a nested query.

I kind of did a mock up database based on the info you provided (its attached). This is the nested query. It basically pulls the timediff information for the corresponding status 3 record for each status 1 record.

SELECT tblAlarms.ID, tblAlarms.alarmdatetm, tblAlarms.MsgNbr, tblAlarms.status, (SELECT TOP 1 Q1.timediff FROM tblAlarms as Q1 WHERE Q1.msgnbr=tblAlarms.msgnbr and Q1.alarmdatetm >= tblAlarms.alarmdatetm and Q1.status=3 Order by Q1.msgnbr, Q1.alarmdatetm ASC) AS Status1to3TimeDiff
FROM tblAlarms
WHERE (((tblAlarms.status)=1));

From here, I assume that you have to run an update query and update those records where the timediff<180. If I were to assume that the reason field is in the alarms table, then this is what the query would look like:

UPDATE tblAlarms INNER JOIN qryGetStatus1andCorrespondingStatus3WithDiff ON (tblAlarms.MsgNbr=qryGetStatus1andCorrespondingStatus3WithDiff.MsgNbr) AND (tblAlarms.alarmdatetm=qryGetStatus1andCorrespondingStatus3WithDiff.alarmdatetm) SET tblAlarms.reason = "My reason"
WHERE (((qryGetStatus1andCorrespondingStatus3WithDiff.Status1to3TimeDiff)<=180));

Hopefully this is what you were after.


I forgot to mention, SQL always uses the mm/dd/yyyy format for dates, so if things with the queries do not work correctly, you may need to convert the dates to numbers using the csng() function.

 

[SteveSchapel]

SQL always uses the mm/dd/yyyy format for dates...

Sorry to butt in here. Just to clarify... should be "VBA uses the mm/dd/yyyy format for dates". If you're talking SQL as in queries, there's no hassle, the regional settings will dictate.

 

[Finchy]

Thanks alot.

I am back at work next week and I will try it out.

I have made a bit of a mistake though not sure if it will alter anything...

Status 1 = Error Occured
Status 2 = Error Solved and
status 3 = Error ack.......

Does this make a difference?

 

[The_Doc_Man]

Whoa! If those explanations are always the same, IGNORE them. Make a lookup table for the alarm field that can be 1, 2, or 3 and JOIN to the lookup table when you do the export.

Or did you mean something else when talking about going back to fill in explanations?

 

[Finchy]

OK, if I understand you correctly, you need to find the corresponding status 3 record for each status 1 record. From there you need to determine if the timediff is <=180 seconds. If so, you want to update the "reason" to some prescribed text. The main question I had is where is the field that will hold the reason? Is it in the same table that holds the alarm data you provided (as shown below)?

14/07/2010 00:06:05/51040/0/14646185/1
14/07/2010 00:07:00/51040/55/14646186/2
14/07/2010 00:07:08/51040/63/14646187/3

I think the way to handle this is with a nested query.

I kind of did a mock up database based on the info you provided (its attached). This is the nested query. It basically pulls the timediff information for the corresponding status 3 record for each status 1 record.

SELECT tblAlarms.ID, tblAlarms.alarmdatetm, tblAlarms.MsgNbr, tblAlarms.status, (SELECT TOP 1 Q1.timediff FROM tblAlarms as Q1 WHERE Q1.msgnbr=tblAlarms.msgnbr and Q1.alarmdatetm >= tblAlarms.alarmdatetm and Q1.status=3 Order by Q1.msgnbr, Q1.alarmdatetm ASC) AS Status1to3TimeDiff
FROM tblAlarms
WHERE (((tblAlarms.status)=1));

From here, I assume that you have to run an update query and update those records where the timediff<180. If I were to assume that the reason field is in the alarms table, then this is what the query would look like:

UPDATE tblAlarms INNER JOIN qryGetStatus1andCorrespondingStatus3WithDiff ON (tblAlarms.MsgNbr=qryGetStatus1andCorrespondingStatus3WithDiff.MsgNbr) AND (tblAlarms.alarmdatetm=qryGetStatus1andCorrespondingStatus3WithDiff.alarmdatetm) SET tblAlarms.reason = "My reason"
WHERE (((qryGetStatus1andCorrespondingStatus3WithDiff.Status1to3TimeDiff)<=180));

Hopefully this is what you were after.


I forgot to mention, SQL always uses the mm/dd/yyyy format for dates, so if things with the queries do not work correctly, you may need to convert the dates to numbers using the csng() function.

To answer your question about where the reason is held: It is stored in another table and it uses the counter number as the reference. At the end of the day all this data is collected together and exported in an excel file.

Like I said in my previous post, I will try later in the week, I am back at work today but I am a little busy and this is a side project. Really grateful for you taking the time to help me though. I will let you know how I get on.

Finchy

 

[jzwp22]

OK, you will have to modify the following query to include the counter number

SELECT tblAlarms.ID, tblAlarms.alarmdatetm, tblAlarms.MsgNbr, tblAlarms.status, (SELECT TOP 1 Q1.timediff FROM tblAlarms as Q1 WHERE Q1.msgnbr=tblAlarms.msgnbr and Q1.alarmdatetm >= tblAlarms.alarmdatetm and Q1.status=3 Order by Q1.msgnbr, Q1.alarmdatetm ASC) AS Status1to3TimeDiff
FROM tblAlarms
WHERE (((tblAlarms.status)=1));

Now, if there is a record in the table that holds the reason already (based on the counter number) you will have to adjust the update query accordingly. If there is not record already, you'll have to change from an update query to an append query.

 

[Finchy]

Hello all

I would like to thank you all for your help,

With the help of someone at work (who saw me type it out) the problem has been solved with the following SQL code. Its tested and working....

SELECT DBA_OfflineAlarmsTable.Counter, Min(DBA_OfflineAlarmsTable.MsgNr) AS MESSAGE_NUMBER, Min(DateDiff('s',([DBA_OfflineAlarmsTable].[DateTime]),([DBA_OfflineAlarmsTable_1].[DateTime]))) AS ALARM_LENGTH
FROM DBA_OfflineAlarmsTable INNER JOIN DBA_OfflineAlarmsTable AS DBA_OfflineAlarmsTable_1 ON DBA_OfflineAlarmsTable.MsgNr=DBA_OfflineAlarmsTable_1.MsgNr
WHERE (((DBA_OfflineAlarmsTable.Counter)<DBA_OfflineAlarmsTable_1.Counter) And (((DBA_OfflineAlarmsTable.Status))=1) And (((DBA_OfflineAlarmsTable_1.Status))=2))
GROUP BY DBA_OfflineAlarmsTable.Counter
ORDER BY DBA_OfflineAlarmsTable.Counter;

Finchy

 

[jzwp22]

Glad you got it worked out. Good luck!