How do I format a query this diverse?

[DAB1477]

Thanks in advance for the assist.

I have a form that collects downtime into a master table.
The form has 5 distinct fields to lace the downtime cause
and a separate field for the associated minutes. It looks
like this:

Downtime cause 1 Minutes
Downtime cause 2 Minutes
Downtime Cause 3 Minutes
Downtime Cause 4 Minutes
Downtime Cause 5 Minutes

Each of the above downtime causes is a drop down box
reading from a Master List of Downtime causes. This data
enters into a Master Table that looks like this:

Downtime Cause 1 Minutes Downtime Cause 2 Minutes....etc.

My issue is this:

How do I query the master table to sum all similar Causes
and minutes from each Downtime column?

For instance: Downtime Cause2 and Downtime Cause 4 may
have the same cause but different minutes. I want to
query to get reason "A" "sum of x minutes" from the list
of all downtime records. Ultimately, I want to pareto the
reasons and minutes to show me the major issues with
Downtime.

Thanks again
Dave B

 

[Dale Fye]

This is going to be messy.

It would be significantly easier if your table was normalized so that
you have maybe four columns, like depicted below, where IncidentID
links to an Incidents table where you record the information that is
specific to each incident. The advantage of having this second table
(tblIncidentCauses) is that you are no longer restricted to just 5
causes for a particular incident, and it becomes very easy to create
statistical informat about the causes.

IncidentID
CauseOrdinal
DownTimeCause
Minutes

Having said that, you can create a query to virtually normalize the
data.

SELECT IncidentID
, 1 as CauseOrdinal
, DownTimeCause1 as DownTimeCause
, Minutes1 as Minutes
FROM yourTable
UNION
SELECT IncidentID
, 2 as CauseOrdinal
, DownTimeCause2 as DownTimeCause
, Minutes2 as Minutes
FROM yourTable
UNION
....
SELECT IncidentID
, 5 as CauseOrdinal
, DownTimeCause5 as DownTimeCause
, Minutes5 as Minutes
FROM yourTable

Once you have this query written and saved as qryNormalizedDownTime,
you can create a new query that does your summation for you.

SELECT DownTimeCause, SUM(Minutes) as CumDownTime
FROM qryNormalizedDownTime
GROUP BY DownTimeCause

--
HTH

Dale Fye


Thanks in advance for the assist.

I have a form that collects downtime into a master table.
The form has 5 distinct fields to lace the downtime cause
and a separate field for the associated minutes. It looks
like this:

Downtime cause 1 Minutes
Downtime cause 2 Minutes
Downtime Cause 3 Minutes
Downtime Cause 4 Minutes
Downtime Cause 5 Minutes

Each of the above downtime causes is a drop down box
reading from a Master List of Downtime causes. This data
enters into a Master Table that looks like this:

Downtime Cause 1 Minutes Downtime Cause 2 Minutes....etc.

My issue is this:

How do I query the master table to sum all similar Causes
and minutes from each Downtime column?

For instance: Downtime Cause2 and Downtime Cause 4 may
have the same cause but different minutes. I want to
query to get reason "A" "sum of x minutes" from the list
of all downtime records. Ultimately, I want to pareto the
reasons and minutes to show me the major issues with
Downtime.

Thanks again
Dave B

 

[John Spencer (MVP)]

One small suggestion - you might want to use UNION ALL vice UNION. I don't
think it will make a difference in the records returned (in this case), but it
should be a bit faster as Access won't try to return just the distinct rows.

 

[dab1477]

Thank you. The Union All worked well. The instructions
were easy to follow for a novice like me. Thanks again.