create a two column table from query results

[Mike Brearley]

I need to create a table that contains two columns, downtime reasons and
downtime amounts (sum), that are based on a query that sums up the downtime
minutes.

The query rows (downtime reason and downtime sum) need to be flipped so that
they are now the tables columns in order to create a Pareto graph. (for a
pareto graph the items listed must be from highest to lowest in order to be
able to easily see what your greatest problem area is.)

Any help would be appreciated.

Thanks,
Mike

 

[Guest]

Hi Mike--Have you tried using a Make-Table Query? I'm not sure what you mean
by this statement: "The query rows (downtime reason and downtime sum) need to
be flipped so that "...what do you mean by flipped?

Where are the downtime reasons and the downtime amounts stored? Also, post
your SQL for you current query and any other relevant information such as
table name(s) and field names.

FYI - It doesn't matter what order the records are stored in the table.
When it is time to display the data, you handle this part through the query.

 

[Mike Brearley]

The query produces results like this:

No Material Tool Replacement - Drill Full Changeover... etc...
0 15
0

and I need the table to be like this:

Reason Downtime
No Material 0
Tool Replacement - Drill 15
Full Changeover 0
etc...

I can then sort by Downtime and create a pareto chart based on the new table

I either need the info in a table or query... I don't care which.

 

[Mike Brearley]

Ok... so I think I'm working on a solution, but I feel real sorry for the
next person that would have to work on this database if I left because...
WOW... this is crazy.

In my query, I'm adding columns, Reason1, Reason2, Reason3, that return the
Reason such as Breaks, No Material, etc...
Then in my form, I create a button that will execute SQL commands such as
this:

DoCmd.RunSQL "INSERT INTO TBL_Downtime (Reason, Amount) SELECT Reason7,
SumOfBreaks FROM QRY_Downtime_Report_By_Date"

I will first clear the table, then I do one of these SQL Insert into table
for each and every reason I have, then I can open a report that will have a
graph based on the table.

Seems like there should be a simpler solution, but until I can find it or
someone can help, I will do it this way. I have to have this database up
and running by the end of the month... pressure is on from the President of
the Irwin to get this done.

 

[Duane Hookom]

Have you considered creating a crosstab query?
Add a new expression as the Row heading
SumOfMinutes: "Total"

Set the Downtime Reasons as the Column Heading and Sum of downtime amounts
as the Value.