Question Merge records

[Qwee]

Hello,

I have ODBC database reporting faults in automated crane system.

Fields I'm using:
ActiveTime - When fault started
EventTime - When fault was resolved
Expr1 - DateDiff("n",[Active Time],[EventTime]) - duration of fault
Message - fault description
area - area where fault occurred

I use append query to save all entries in new table. Query is indexed with ActiveTime and Message to avoid duplicated entries when appending.

Issue
Sometimes I have multiple faults with different 'Message' but related to same 'area' in the same time frame.

Eg.
3 different messages:
* Crane 5 Err 21 General 15 Emerg shutdown software Fault
start 16:07 finished 16:17

* 2JA2 Pickup Not Complete in Time Fault
start 16:16 finished 16:30

* Crane 5 Err 55 General 139 E-Stop safety relay no feedback Fault
start 16:18 finished 16:30


All related to same area "Crane 5"

What I need is

Area Crane 5 ActiveTime 16:07 (minimum time) EventTime 16:30 (maximum time)

I can't group them and use minimum/maximum expression as other faults can occur in the meantime not related to this issue or another issue in that area can happen after previous one is resolved.

I'm not very knowledgeable in access and everything I learned is this forum here so please be gentle

Thanks in advance.

 

[plog]

I can't follow your delima, nor why you can't use Min/Max. It would be more helpful if you posted your data in a format that was easier to understand what fields you showed us:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
David, 12/1/2014, 1657
Sally, 1/13/2008, 99

Also, include more sample data--enough to cover all cases, enough to accurately portray your problem and enough noise so we can see how to weed it out.


So, provide 2 sets of data:

A. Starting sample data. In the format I demonstrated.

B. Expected results based on A.

 

[spikepl]

other faults can occur in the meantime not related to this issue or another issue in that area can happen after previous one is resolved.

DO you have an algorithm that will decide for you which faults are related to one area and issue? If not then a person has to do it. In this case you need to create the mechanism on a form for a person to tell the system which faults are related. Once done, then it should be easy to list/sum related faults.

I think you need to prepare cases: one for each possible combination of faults and areas, related, unrelated, fixed (and other possibly relevant cirumstances): what data is there and what is to be done with it. One example is not enough to cover all that. Once you have that then ask for help if still stuck.

I am not sure that merging data is such a great thing on its own - summarising perhaps. Another issue - perhaps taken care of upstream - is the classification of faults in severity, and dependence (or not) since some faults derive from previous faults, some arise independently, yet still affect same area.

 

[Qwee]

Hello,

I have ODBC database reporting faults in automated crane system.

Fields I'm using:
ActiveTime - When fault started
EventTime - When fault was resolved
Expr1 - DateDiff("n",[Active Time],[EventTime]) - duration of fault
Message - fault description
area - area where fault occurred

I use append query to save all entries in new table. Query is indexed with ActiveTime and Message to avoid duplicated entries when appending.

Issue
Sometimes I have multiple faults with different 'Message' but related to same 'area' in the same time frame.

Eg.
3 different messages:
* Crane 5 Err 21 General 15 Emerg shutdown software Fault
start 16:07 finished 16:17

* 2JA2 Pickup Not Complete in Time Fault
start 16:16 finished 16:30

* Crane 5 Err 55 General 139 E-Stop safety relay no feedback Fault
start 16:18 finished 16:30


All related to same area "Crane 5"

What I need is

Area Crane 5 ActiveTime 16:07 (minimum time) EventTime 16:30 (maximum time)

I can't group them and use minimum/maximum expression as other faults can occur in the meantime not related to this issue or another issue in that area can happen after previous one is resolved.

I'm not very knowledgeable in access and everything I learned is this forum here so please be gentle

Thanks in advance.

Table name:
AMHEfaults

Fields
ActiveTime, EventTime, area






A.
ActiveTime EventTime area
15/10/2015 16:18 15/10/2015 16:30 Crane 5
15/10/2015 16:16 15/10/2015 16:30 Crane 5
15/10/2015 16:07 15/10/2015 16:17 Crane 5
15/10/2015 12:24 15/10/2015 13:11 Crane 8
15/10/2015 12:24 15/10/2015 13:11 Crane 8
15/10/2015 12:24 15/10/2015 13:11 Crane 8
15/10/2015 12:24 15/10/2015 13:11 Crane 8
15/10/2015 12:24 15/10/2015 13:11 Crane 8
15/10/2015 12:24 15/10/2015 13:11 Crane 8
15/10/2015 12:24 15/10/2015 13:11 Crane 8
15/10/2015 12:24 15/10/2015 13:11 Crane 8
15/10/2015 12:24 15/10/2015 13:11 Crane 8
15/10/2015 12:24 15/10/2015 13:11 Crane 8
15/10/2015 12:24 15/10/2015 13:12 Crane 8
15/10/2015 12:24 15/10/2015 13:15 Crane 8
15/10/2015 12:24 15/10/2015 13:19 Crane 8
15/10/2015 12:24 15/10/2015 13:12 Crane 8
15/10/2015 12:24 15/10/2015 13:12 Crane 8
15/10/2015 12:24 15/10/2015 13:12 Crane 8
15/10/2015 12:24 15/10/2015 13:12 Crane 8
15/10/2015 12:24 15/10/2015 13:12 Crane 8
15/10/2015 12:24 15/10/2015 13:12 Crane 8
15/10/2015 12:19 15/10/2015 13:12 Crane 8
15/10/2015 12:19 15/10/2015 13:12 Crane 8
15/10/2015 12:19 15/10/2015 13:11 Crane 8
15/10/2015 12:13 15/10/2015 13:36 Crane 8
15/10/2015 12:13 15/10/2015 13:36 Crane 8
15/10/2015 12:13 15/10/2015 13:11 Crane 8
15/10/2015 05:32 15/10/2015 05:40 Crane 4
15/10/2015 05:32 15/10/2015 05:40 Crane 4
15/10/2015 05:32 15/10/2015 05:40 Crane 4
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 04:54 15/10/2015 05:03 Crane 4
15/10/2015 04:54 15/10/2015 05:03 Crane 4
15/10/2015 04:54 15/10/2015 05:03 Crane 4


B.
ActiveTime EventTime area
15/10/2015 16:18 15/10/2015 16:17 Crane 5
15/10/2015 12:13 15/10/2015 13:36 Crane 8
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 04:54 15/10/2015 05:03 Crane 4

Reason why i can't use groups and min/max are two last examples for crane 4. I need them separate.


Many thanks

 

[Qwee]

DO you have an algorithm that will decide for you which faults are related to one area and issue?

For same area, if start time of second record fall before finish time of first record it's related.

 

[plog]

For same area, if start time of second record fall before finish time of first record it's related.

That's not good enough. By that logic, these 2 events are related:

ActiveTime EventTime area
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 04:54 15/10/2015 05:03 Crane 4

Now before you clearify your response, what happens if you have this:


ActiveTime EventTime area
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 04:54 15/10/2015 05:03 Crane 4
15/10/2015 05:00 15/10/2015 05:17 Crane 4
15/10/2015 05:14 15/10/2015 05:32 Crane 4

How many records do you expect in your final query? And what times do you expect to appear?

 

[Qwee]

That's not good enough. By that logic, these 2 events are related:

ActiveTime EventTime area
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 04:54 15/10/2015 05:03 Crane 4

Now before you clearify your response, what happens if you have this:


ActiveTime EventTime area
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 04:54 15/10/2015 05:03 Crane 4
15/10/2015 05:00 15/10/2015 05:17 Crane 4
15/10/2015 05:14 15/10/2015 05:32 Crane 4

How many records do you expect in your final query? And what times do you expect to appear?

Sorry I'm at work and rushing a bit.

List should be increasing not decreasing so:

ActiveTime EventTime area
15/10/2015 04:54 15/10/2015 05:03 Crane 4
15/10/2015 05:30 15/10/2015 05:40 Crane 4

Which means 05:30 fall after 05:03 not before that's why it's split in two records.

Regarding to your question

ActiveTime EventTime area
15/10/2015 05:30 15/10/2015 05:40 Crane 4
15/10/2015 04:54 15/10/2015 05:03 Crane 4
15/10/2015 05:00 15/10/2015 05:17 Crane 4
15/10/2015 05:14 15/10/2015 05:32 Crane 4

I expect
15/10/2015 04:54 15/10/2015 05:40 Crane 4

 

[plog]

Now I fully understand and the answer isn't a simply query. This is going to involve VBA and recordsets. The problem is that you can't just look at a record, or even just 2 and now how they relate. In that example I gave the record that occurs first (4:54 - 5:03) in no way touches the one that occurs last (5:30 - 5:40), but because there are records in between them that bridge the two, you do want them to relate. That's gonna take more than a query to achieve.

This isn't beginner level stuff--medium level, and a fair amount of work. I can point you in the right direction--google Recordsets and VBA. You are going to have to loop through all similar area records to find out exactly how they match by keeping track of the highest and lowest time frames and which ones overlap.

 

[pbaldy]

Not sure why Qwee had 2 posts moderated, but I deleted them as post 4 seemed to be the same.

 

[Qwee]

Now I fully understand and the answer isn't a simply query. This is going to involve VBA and recordsets. The problem is that you can't just look at a record, or even just 2 and now how they relate. In that example I gave the record that occurs first (4:54 - 5:03) in no way touches the one that occurs last (5:30 - 5:40), but because there are records in between them that bridge the two, you do want them to relate. That's gonna take more than a query to achieve.

This isn't beginner level stuff--medium level, and a fair amount of work. I can point you in the right direction--google Recordsets and VBA. You are going to have to loop through all similar area records to find out exactly how they match by keeping track of the highest and lowest time frames and which ones overlap.

Thanks plog. I'll do my research and I'll post my questions here if that's OK.

 

[Qwee]

I was wondering if this couldn't be handled by SQL by any chance?


What I have so far is:

SELECT s1.[ActiveTime],
MIN(t1.[EventTime]) AS EndTime
FROM AMHEfault s1
INNER JOIN AMHEfault t1 ON s1.[ActiveTime] <= t1.[EventTime]
AND NOT EXISTS(SELECT * FROM AMHEfault t2
WHERE t1.[EventTime] >= t2.[ActiveTime] AND t1.[EventTime] < t2.[EventTime])
WHERE NOT EXISTS(SELECT * FROM AMHEfault s2
WHERE s1.[ActiveTime] > s2.[ActiveTime] AND s1.[ActiveTime] <= s2.[EventTime])
GROUP BY s1.[ActiveTime]
ORDER BY s1.[ActiveTime]


But it's coming up with Syntax error in query expression ".