How to get the production downtime I need from "ALARM Database"? | Newbie | | Join Date: Dec 2008
Posts: 13
| |
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. |  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
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: - '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
- '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? | | Newbie | | Join Date: Dec 2008
Posts: 13
| | | re: How to get the production downtime I need from "ALARM Database"?
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
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?
| | Newbie | | Join Date: Dec 2008
Posts: 13
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
Originally Posted by superleochen I export about 400 Records. Hope it helps.
thanks. Just for curiosity, where are the 400 Records? (LOL).
| | Newbie | | Join Date: Dec 2008
Posts: 13
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
Originally Posted by ADezii Just for curiosity, where are the 400 Records? (LOL). sorry.....i guess I mis-pressed some button-.-
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
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:
- [ID] - (LONG)
- [TIME] - (DATE/TIME)
- [TAGNAME] - (TEXT 50)
- [VALUE] - (TEXT 25)
- The Temporary Results Table is named tblTempResults and consists of:
- [TAGNAME] - (TEXT 50)
- [Downtime] - (LONG)
- 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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Here are the results with the 400 Records appended.
| | Newbie | | Join Date: Dec 2008
Posts: 13
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
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~~~
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
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.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: How to get the production downtime I need from "ALARM Database"?
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
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. Quote:
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.
| | Newbie | | Join Date: Dec 2008
Posts: 13
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
Originally Posted by superleochen 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. 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).
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
Originally Posted by NeoPa 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. Thanks NoePa, you opinion is always welcome, and of course, valued.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
Originally Posted by superleochen 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. 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.
|  | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: How to get the production downtime I need from "ALARM Database"?
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)
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
Originally Posted by nico5038 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) 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.
| | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: How to get the production downtime I need from "ALARM Database"?
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)
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
Originally Posted by nico5038 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) 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.
| | Newbie | | Join Date: Dec 2008
Posts: 13
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
Originally Posted by nico5038 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) You guys rock, man~ This should be my best Christmas present ever~
Thank you and Merry Christmas~
| | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: How to get the production downtime I need from "ALARM Database"?
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)
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: How to get the production downtime I need from "ALARM Database"?
Quote:
Originally Posted by nico5038 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) Thanks Nico, have a nice Holiday!
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
