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

**ADezii** · Dec 19 '08, 01:12 AM

Originally posted by superleochen

Database is simliar to this:
ID TIME TAGNAME VALUE
5967383 8/1/2008 1:00:24 AM I16MUDL11 ALARM
5967384 8/1/2008 1:00:24 AM I16MUDL09 ALARM
5967391 8/1/2008 1:00:32 AM I16MUDL11 A_OK
5967392 8/1/2008 1:00:32 AM I16MUDL09 A_OK
5967398 8/1/2008 1:00:36 AM I16MUDL11 ALARM
5967406 8/1/2008 1:01:10 AM I16MUDL11 A_OK
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

It's driving me crazy. Please please give me a helping hand.

Just so we are crystal clear, the Breakdown Time for Tagname I16MUDL11 would be 42 seconds, namely:

Code:

'8 seconds between ALARM and A_OK for I16MUDL11
5967383 8/1/2008 1:00:24 AM I16MUDL11 ALARM
5967391 8/1/2008 1:00:32 AM I16MUDL11 A_OK

Code:

'34 seconds between ALARM and A_OK for I16MUDL11
5967398 8/1/2008 1:00:36 AM I16MUDL11 ALARM
5967406 8/1/2008 1:01:10 AM I16MUDL11 A_OK

Is this assumption True?

**superleochen** · Dec 19 '08, 01:41 AM

Thank you~

Thank you, ADezii~

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.

Appreciate your help.

**ADezii** · Dec 19 '08, 11:49 AM

Originally posted by superleochen

Thank you, ADezii~

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.

Appreciate your help.

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?

**superleochen** · Dec 19 '08, 02:45 PM

Records

Originally posted by ADezii

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?

I export about 400 Records. Hope it helps.

thanks.

**ADezii** · Dec 19 '08, 09:35 PM

Originally posted by superleochen

I export about 400 Records. Hope it helps.

thanks.

Just for curiosity, where are the 400 Records? (LOL).

**superleochen** · Dec 19 '08, 09:46 PM

Originally posted by ADezii

Just for curiosity, where are the 400 Records? (LOL).

sorry.....i guess I mis-pressed some button-.-

Attached Files

machine alarm1.zip (2.4 KB, 154 views)

**ADezii** · Dec 20 '08, 12:26 AM

Originally posted by superleochen

sorry.....i guess I mis-pressed some button-.-

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.

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)
The Temporary Results Table is named tblTempResults and consists of:
1. [TAGNAME] - (TEXT 50)
2. [Downtime] - (LONG)

Code:

Dim strSQL As String
Dim strSQL_2 As String
Dim MyDB As DAO.Database
Dim rstUniqueTagNames As DAO.Recordset
Dim rstParts As DAO.Recordset
Dim rstClone As DAO.Recordset
Dim rstTempResults As DAO.Recordset

strSQL = "Select Distinct tblParts.[TAGNAME] From tblParts;"

CurrentDb.Execute "Delete * From tblTempResults;", dbFailOnError

Set MyDB = CurrentDb()

Set rstUniqueTagNames = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
Set rstTempResults = MyDB.OpenRecordset("tblTempResults", dbOpenDynaset, dbAppendOnly)

Debug.Print "***************************************************************"

DoCmd.Hourglass True

With rstUniqueTagNames
  Do While Not .EOF
    strSQL_2 = "Select * From tblParts Where tblParts![TAGNAME] = '" & ![TAGNAME] & "';"
      Set rstParts = MyDB.OpenRecordset(strSQL_2, dbOpenSnapshot)
      Set rstClone = rstParts.Clone
        rstParts.MoveFirst: rstClone.Move 1
          Do While Not rstClone.EOF
            If rstParts![Value] = "ALARM" And rstClone![Value] = "A_OK" Then
              rstTempResults.AddNew
                rstTempResults![TAGNAME] = ![TAGNAME]
                rstTempResults![Downtime] = DateDiff("s", rstParts![Time], rstClone![Time])
                  Debug.Print ![TAGNAME] & " ==> " & rstParts![TAGNAME] & " ==> " & rstClone![TAGNAME] & _
                              " ==> " & DateDiff("s", rstParts![Time], rstClone![Time])
              rstTempResults.Update
            Else
            End If
            rstParts.MoveNext
            rstClone.MoveNext
          Loop
      .MoveNext
      rstParts.MoveFirst
      rstClone.MoveFirst: rstClone.Move 1
  Loop
End With

Debug.Print "***************************************************************"

DoCmd.Hourglass False

rstUniqueTagNames.Close
rstParts.Close
rstClone.Close
rstTempResults.Close
Set rstUniqueTagNames = Nothing
Set rstParts = Nothing
Set rstClone = Nothing
Set rstTempResults = Nothing

DoCmd.OpenTable "tblTempResults", acViewNormal, acReadOnly
DoCmd.Maximize

P.S. - I'll download the Attachment and wait for your response.

**ADezii** · Dec 20 '08, 12:42 AM

Here are the results with the 400 Records appended.

**superleochen** · Dec 20 '08, 03:27 PM

Seems very complicated....

Originally posted by ADezii

Here are the results with the 400 Records appended.

Thanks a lot. I need sometime to understand your solution. I will let you know whether it works out or not.

Again, Appreciate your help, ADezii~~~

**ADezii** · Dec 20 '08, 11:53 PM

Originally posted by superleochen

Thanks a lot. I need sometime to understand your solution. I will let you know whether it works out or not.

Again, Appreciate your help, ADezii~~~

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.

**NeoPa** · Dec 22 '08, 02:23 PM

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.

**ADezii** · Dec 22 '08, 05:23 PM

Originally posted by NeoPa

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.

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.

**superleochen** · Dec 22 '08, 07:29 PM

Originally posted by ADezii

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.

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.

**NeoPa** · Dec 22 '08, 10:22 PM

Originally posted by ADezii

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.

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.

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment