A
Al Camp
Well folks, this is going to be a real toughie...
I have a table that tracks down time for 40 machines. A record is
opened when a "down" Incident DateTime occurs, and that record is "closed out" when
a RepairEnd Date/Time is entered. I have a query that lists every Incident,
and the associated down hours from IncidentStartDateTime to IncidentEndDateTime.
IncNo MachNo IncDate DownType DownHrs
23 1 1/1/05 F 50.0**
24 4 1/1/05 M 4.3**
25 4 1/1/05 PM 1.1
26 3 1/2/05 Q .9** etc.....
** largest downtime/per day/per machine
I have created a crosstab query (w/date range) that lists every day of the
date range Down... and every machine Across. For each day, I post the
DownType that accounted for the largest down time that day.
Date Mach1 Mach2 Mach3 Mach4........ Mach40
1/1/05 F M
1/2/05 Q
.....etc for all days in date range.
The problem is that IncNo 23 exceeds 24 hours, with 24 hours of outage going on into
1/2/05, and 2 hours going on into 1/3/05. IF that 2 hour "remainder" outage was the
greatest against Mach1 for 1/2/05, the crosstab should show...
Date Mach1 Mach2 Mach3 Mach4........ Mach40
1/1/05 F F F M
1/2/05 Q
Is it possible to make a copy of my Outage table, and "operate" upon it to somehow break
these "over 24 hour" outages into two or more entries o so that my
pre-crosstab data looks like this...
IncNo MachNo IncDate DownType DownHrs
23 1 1/1/05 F 36.0** < leave original
but...
23 1 1/2/05 F 24.0** < carryover to next
day/s
23 1 1/3/05 F 12.0** < remainder
24 4 1/1/05 M 4.3**
25 4 1/1/05 PM 1.1
26 3 1/2/05 Q .9**
Whew!!
I know it's complicated, but this is a critical report, and it will save many hours of
manual data collection, so I think it's worth it.
My plan is to try...
Copy original data to a "Temp" working table... Run a MakeTable against Temp to break out
the >24... Run an Append of the Make Table to the Temp.
Is this a reasonable way to go? Any suggestions or known pitfalls?
Thank you for any assistance...
Al Camp
I have a table that tracks down time for 40 machines. A record is
opened when a "down" Incident DateTime occurs, and that record is "closed out" when
a RepairEnd Date/Time is entered. I have a query that lists every Incident,
and the associated down hours from IncidentStartDateTime to IncidentEndDateTime.
IncNo MachNo IncDate DownType DownHrs
23 1 1/1/05 F 50.0**
24 4 1/1/05 M 4.3**
25 4 1/1/05 PM 1.1
26 3 1/2/05 Q .9** etc.....
** largest downtime/per day/per machine
I have created a crosstab query (w/date range) that lists every day of the
date range Down... and every machine Across. For each day, I post the
DownType that accounted for the largest down time that day.
Date Mach1 Mach2 Mach3 Mach4........ Mach40
1/1/05 F M
1/2/05 Q
.....etc for all days in date range.
The problem is that IncNo 23 exceeds 24 hours, with 24 hours of outage going on into
1/2/05, and 2 hours going on into 1/3/05. IF that 2 hour "remainder" outage was the
greatest against Mach1 for 1/2/05, the crosstab should show...
Date Mach1 Mach2 Mach3 Mach4........ Mach40
1/1/05 F F F M
1/2/05 Q
Is it possible to make a copy of my Outage table, and "operate" upon it to somehow break
these "over 24 hour" outages into two or more entries o so that my
pre-crosstab data looks like this...
IncNo MachNo IncDate DownType DownHrs
23 1 1/1/05 F 36.0** < leave original
but...
23 1 1/2/05 F 24.0** < carryover to next
day/s
23 1 1/3/05 F 12.0** < remainder
24 4 1/1/05 M 4.3**
25 4 1/1/05 PM 1.1
26 3 1/2/05 Q .9**
Whew!!
I know it's complicated, but this is a critical report, and it will save many hours of
manual data collection, so I think it's worth it.
My plan is to try...
Copy original data to a "Temp" working table... Run a MakeTable against Temp to break out
the >24... Run an Append of the Make Table to the Temp.
Is this a reasonable way to go? Any suggestions or known pitfalls?
Thank you for any assistance...
Al Camp