469,091 Members | 1,111 Online

# How to get the production downtime I need from "ALARM Database"?

Database is simliar to this:
Expand|Select|Wrap|Line Numbers
1. ID             TIME           TAGNAME VALUE
2. 5967383 8/1/2008 1:00:24 AM I16MUDL11 ALARM
3. 5967384 8/1/2008 1:00:24 AM I16MUDL09 ALARM
4. 5967391 8/1/2008 1:00:32 AM I16MUDL11 A_OK
5. 5967392 8/1/2008 1:00:32 AM I16MUDL09 A_OK
6. 5967398 8/1/2008 1:00:36 AM I16MUDL11 ALARM
7. 5967406 8/1/2008 1:01:10 AM I16MUDL11 A_OK
8. 5967407 8/1/2008 1:01:10 AM I16MUDL09 A_OK

Description:

If the value equals "ALARM", the machine will break down until the corresponding "A_OK" appears. Each ALARM and A_OK have a "TAGNAME" that indicates different parts of the machine.

Goal:
We want to find out how much time we lose in producition due to the breakdown of different parts. So we need to calculate the time between each "ALARM" and "A_OK" for each different "TAGNAME".

Misc.

The database is huge, containing about 600,000 entries.

I need:
TagName Frequency Total Downtime

Dec 18 '08 #1
24 1738
8,800 Expert 8TB
@superleochen
Just so we are crystal clear, the Breakdown Time for Tagname I16MUDL11 would be 42 seconds, namely:
Expand|Select|Wrap|Line Numbers
1. '8 seconds between ALARM and A_OK for I16MUDL11
2. 5967383 8/1/2008 1:00:24 AM I16MUDL11 ALARM
3. 5967391 8/1/2008 1:00:32 AM I16MUDL11 A_OK
Expand|Select|Wrap|Line Numbers
1. '34 seconds between ALARM and A_OK for I16MUDL11
2. 5967398 8/1/2008 1:00:36 AM I16MUDL11 ALARM
3. 5967406 8/1/2008 1:01:10 AM I16MUDL11 A_OK
Is this assumption True?
Dec 19 '08 #2

Your assumption is true. The breatdown time for "Tagname I16MUDL11" is 42 seconds for these 2 breakdowns.

Also, maybe I shouldn't say "Frequency", what I want to get is how many times each machine part, which is "Tagname", break down.

Dec 19 '08 #3
8,800 Expert 8TB
@superleochen
Please be patient and I'll see what I can come up with, the solution right now is not exactly obvious, and I don't think that it will be simple. Can you possibly supply me with more data, say 1,000 Records, contained within a Table that you can Attach to a Post?
Dec 19 '08 #4
I export about 400 Records. Hope it helps.

thanks.
Dec 19 '08 #5
8,800 Expert 8TB
@superleochen
Just for curiosity, where are the 400 Records? (LOL).
Dec 19 '08 #6
sorry.....i guess I mis-pressed some button-.-
Attached Files
 machine alarm1.zip (2.4 KB, 103 views)
Dec 19 '08 #7
8,800 Expert 8TB
@superleochen
Run the following Algorithm on your data, not the 600,000+ Records, but a subset of the Records, say 5,000. The intermediate results will be contained in tblTempResults which will be opened following the processing of the data. We can proceed no further until we know the logic is sound, and the results valid. There is probably an easier approach, but none is aware to me at the moment. To make things easier for you, I've Attached the Test Database for this Thread to this Post. The following assumptions have been made on my part, you must make the appropriate substitutions on your part if they are necessary.
1. The Main Table Name is tblParts consisting of the following Fields:
1. [ID] - (LONG)
2. [TIME] - (DATE/TIME)
3. [TAGNAME] - (TEXT 50)
4. [VALUE] - (TEXT 25)
2. The Temporary Results Table is named tblTempResults and consists of:
1. [TAGNAME] - (TEXT 50)
2. [Downtime] - (LONG)
Expand|Select|Wrap|Line Numbers
1. Dim strSQL As String
2. Dim strSQL_2 As String
3. Dim MyDB As DAO.Database
4. Dim rstUniqueTagNames As DAO.Recordset
5. Dim rstParts As DAO.Recordset
6. Dim rstClone As DAO.Recordset
7. Dim rstTempResults As DAO.Recordset
8.
9. strSQL = "Select Distinct tblParts.[TAGNAME] From tblParts;"
10.
11. CurrentDb.Execute "Delete * From tblTempResults;", dbFailOnError
12.
13. Set MyDB = CurrentDb()
14.
15. Set rstUniqueTagNames = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
16. Set rstTempResults = MyDB.OpenRecordset("tblTempResults", dbOpenDynaset, dbAppendOnly)
17.
18. Debug.Print "***************************************************************"
19.
20. DoCmd.Hourglass True
21.
22. With rstUniqueTagNames
23.   Do While Not .EOF
24.     strSQL_2 = "Select * From tblParts Where tblParts![TAGNAME] = '" & ![TAGNAME] & "';"
25.       Set rstParts = MyDB.OpenRecordset(strSQL_2, dbOpenSnapshot)
26.       Set rstClone = rstParts.Clone
27.         rstParts.MoveFirst: rstClone.Move 1
28.           Do While Not rstClone.EOF
29.             If rstParts![Value] = "ALARM" And rstClone![Value] = "A_OK" Then
31.                 rstTempResults![TAGNAME] = ![TAGNAME]
32.                 rstTempResults![Downtime] = DateDiff("s", rstParts![Time], rstClone![Time])
33.                   Debug.Print ![TAGNAME] & " ==> " & rstParts![TAGNAME] & " ==> " & rstClone![TAGNAME] & _
34.                               " ==> " & DateDiff("s", rstParts![Time], rstClone![Time])
35.               rstTempResults.Update
36.             Else
37.             End If
38.             rstParts.MoveNext
39.             rstClone.MoveNext
40.           Loop
41.       .MoveNext
42.       rstParts.MoveFirst
43.       rstClone.MoveFirst: rstClone.Move 1
44.   Loop
45. End With
46.
47. Debug.Print "***************************************************************"
48.
49. DoCmd.Hourglass False
50.
51. rstUniqueTagNames.Close
52. rstParts.Close
53. rstClone.Close
54. rstTempResults.Close
55. Set rstUniqueTagNames = Nothing
56. Set rstParts = Nothing
57. Set rstClone = Nothing
58. Set rstTempResults = Nothing
59.
61. DoCmd.Maximize
Dec 20 '08 #8
8,800 Expert 8TB
Here are the results with the 400 Records appended.
Dec 20 '08 #9
Thanks a lot. I need sometime to understand your solution. I will let you know whether it works out or not.

Dec 20 '08 #10
8,800 Expert 8TB
@superleochen
If the solution works as intended, Phase II, namely calculating the Frequency and Aggregate Downtime Totals should be a breeze. I'll walk you through the code, if you so desire. Someone may also come up with a shorter, less complex, solution.
Dec 20 '08 #11
NeoPa
32,159 Expert Mod 16PB
I would only suggest you consider the concept of linking the table into the query twice. Once as an ALARM source, and once as an A_OK source.

The two record sources could be linked by Tag and a WHERE clause to stipulate that matches where the A_OK is before the ALARM be ignored.

I do understand this will produce many unrequired pairings, but it will also be appreciably fewer than doing solely via recordset processing in code.

Please ignore if you don't see any potential benefits. It's certainly not as straightforward as it may seem initially.
Dec 22 '08 #12
8,800 Expert 8TB
@NeoPa
It's certainly not as straightforward as it may seem initially.
I agree with you also, NeoPa. I did, however, arrive at a 2 Phase Solution and I would appreciate it if you could look at it and give your honest opinion. Simply Download the Attachment, execute Phase I, then Phase II. Thanks.
Dec 22 '08 #13
Thank you, guys~

I would love to have a recursive query instead of complicated codes. Since the codes are kinda overwhelming for me to understand. But I am still trying..

I do have a minor problem running ADezii's phase I code on the complete records---I saw a minus down time. I am still trying to figure out why.

Again, thanks for you guys' support.
Dec 22 '08 #14
NeoPa
32,159 Expert Mod 16PB
I had a quick look ADezii, but I'm afraid I don't have the time to go through something as complicated as this with a fine-tooth comb.

I ran it and all seemed to progress ok. I can't really do a full deconstruction to assess where it may have a problem. I'm assuming there is a small logic flaw somewhere, as the OP reports a negative value with his original data.

That may be due to the main data being in a slightly different format, or it may appear in a non-ordered sequence. Without access to the data and a fair amount of time I couldn't say :(

From what I could see, of course, it's a perfect solution.
Dec 22 '08 #15
8,800 Expert 8TB
@superleochen
I couldn't agree with you more in stating that you would rather run a Recursive Query rather than complex code, but as I have always stated in the past, SQL is not my strong point. I simply pointed out another option which may/may not be viable in your situation. Be patient and see what the other Moderators/Experts have for suggestions, I'll can in an Assist! (LOL).
Dec 23 '08 #16
8,800 Expert 8TB
@NeoPa
Thanks NoePa, you opinion is always welcome, and of course, valued.
Dec 23 '08 #17
NeoPa
32,159 Expert Mod 16PB
@superleochen
Unless / until someone else comes in to offer an alternative, let me draw your attention to an article on debugging (Debugging in VBA). With this you may be able to go through the code as it executes and find what small flaw there may be in the logic.

It will also enable you to get more familiar with the code. Important if you choose to go forward with it.
Dec 23 '08 #18
nico5038
3,079 Expert 2GB
OK, fiddled a bit around with queries to do the same.
Basically it's sorting the data by tagname, time and ID and storing it in a temp table with an autonumber ID.
Then a query composes an ALARM/A_OK record using the autonumber - 1 to "join" multiple rows.
The rows are filtered to get only "pairs" as some tagname's have only an ALARM and/or A_OK.

I've also created a Delete query to empty the temp table so the process can be repeated.

Nic;o)
Attached Files
 AlarmByQueryNico5038.zip (23.0 KB, 91 views)
Dec 23 '08 #19
8,800 Expert 8TB
@nico5038
Really nice approach, Nico, I just knew there was probably a better Method to solve this problem. I hope you don't mind, but I added a 4th and 5th Query in order to achieve the desired goal. The 4th Query will GROUP on the TAGNAME and calculate the Frequency and Aggregate Totals for the Duration in Seconds. The 5th Query simply formats the results to something more meaningful. Thanks again for your keen insight into this Thread, and kindly get back to me on the additional Queries, since it is your code and solution, and I do not want you to feel like I am stepping on your toes. BTW, I cross-referenced your results with my convoluted and complicated solution, and they are exactly the same.
Dec 23 '08 #20
nico5038
3,079 Expert 2GB
I feel rewarded when people are (re)using my solutions and toe stepping isn't possible in cases like this <LOL>.

I hope that the OP superleochen is satisfied, as that's all that counts.

Regards,

Nic;o)
Dec 24 '08 #21
8,800 Expert 8TB
@nico5038
Hello again, Nico. One thing I forgot to ask and which is really eluding me. In qryPartsProcess01_CreateTemp, what is the reason for the VALUE Field being entered twice in the Query? (once for the actual Append Operation, and again to define the Sort Sequence). Why can't these two operations be combined into a single VALUE Field representation? Thanks.
Dec 24 '08 #22
@nico5038
You guys rock, man~ This should be my best Christmas present ever~

Thank you and Merry Christmas~
Dec 24 '08 #23
nico5038
3,079 Expert 2GB
The last sort column can be removed. I started with a sort on the three fields until I found that ALARM and A_OK could occur several times with the same date time VALUE, so I switched to using the ID.

superleochen,
Glad we could help and a Merry Christmas too.
One final tip, don't use "Value" as a field name, it's a reserved word and can cause trouble. I always add the "nature" of a date time field, like "AlarmDateTime" or "AlarmTimestamp".

Nic;o)
Dec 24 '08 #24