By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

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

P: 13
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


It's driving me crazy. Please please give me a helping hand.
Dec 18 '08 #1
Share this Question
Share on Google+
24 Replies


ADezii
Expert 5K+
P: 8,638
@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

P: 13
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.
Dec 19 '08 #3

ADezii
Expert 5K+
P: 8,638
@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

P: 13
@ADezii
I export about 400 Records. Hope it helps.

thanks.
Dec 19 '08 #5

ADezii
Expert 5K+
P: 8,638
@superleochen
Just for curiosity, where are the 400 Records? (LOL).
Dec 19 '08 #6

P: 13
@ADezii
sorry.....i guess I mis-pressed some button-.-
Attached Files
File Type: zip machine alarm1.zip (2.4 KB, 77 views)
Dec 19 '08 #7

ADezii
Expert 5K+
P: 8,638
@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
  30.               rstTempResults.AddNew
  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.  
  60. DoCmd.OpenTable "tblTempResults", acViewNormal, acReadOnly
  61. DoCmd.Maximize
P.S. - I'll download the Attachment and wait for your response.
Dec 20 '08 #8

ADezii
Expert 5K+
P: 8,638
Here are the results with the 400 Records appended.
Dec 20 '08 #9

P: 13
@ADezii
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~~~
Dec 20 '08 #10

ADezii
Expert 5K+
P: 8,638
@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
Expert Mod 15k+
P: 31,492
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

ADezii
Expert 5K+
P: 8,638
@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

P: 13
@ADezii
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
Expert Mod 15k+
P: 31,492
@ADezii
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

ADezii
Expert 5K+
P: 8,638
@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

ADezii
Expert 5K+
P: 8,638
@NeoPa
Thanks NoePa, you opinion is always welcome, and of course, valued.
Dec 23 '08 #17

NeoPa
Expert Mod 15k+
P: 31,492
@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
Expert 2.5K+
P: 3,072
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
File Type: zip AlarmByQueryNico5038.zip (23.0 KB, 81 views)
Dec 23 '08 #19

ADezii
Expert 5K+
P: 8,638
@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
Expert 2.5K+
P: 3,072
Glad you appreciate the approach ADezii :-)
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

ADezii
Expert 5K+
P: 8,638
@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

P: 13
@nico5038
You guys rock, man~ This should be my best Christmas present ever~

Thank you and Merry Christmas~
Dec 24 '08 #23

nico5038
Expert 2.5K+
P: 3,072
Hi ADezii,
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

ADezii
Expert 5K+
P: 8,638
@nico5038
Thanks Nico, have a nice Holiday!
Dec 24 '08 #25

Post your reply

Sign in to post your reply or Sign up for a free account.