Multiple queries -> one report?

[diddydustin]

Hey guys,

I'm using the following query:

SELECT Sum(EMR_TotalGraphicTime.[Sum Of Time_Total]) AS [SUM], EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code
FROM EMR_EmployeeHistory INNER JOIN EMR_TotalGraphicTime ON EMR_EmployeeHistory.Time_ID = EMR_TotalGraphicTime.Time_ID
WHERE EMR_TotalGraphicTime.Time_Code In (100,900)
GROUP BY EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code
UNION
SELECT Sum(EMR_TotalGraphicTime.[Sum Of Time_Total]) AS [SUM], EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, 0
FROM EMR_EmployeeHistory INNER JOIN EMR_TotalGraphicTime ON EMR_EmployeeHistory.Time_ID = EMR_TotalGraphicTime.Time_ID
WHERE EMR_TotalGraphicTime.Time_Code Not In (100,900)
GROUP BY EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder
;


However, this query gives me one column of SUM values for time codes either 100, 900, or 0. In my report, I want to be able to break this down and have three columns, one for each time code. How can I do this?

Thank you
Dustin

 

[dhookom]

Could you use a crosstab with the Time_Code field as the Column Heading?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

Instead of using the time_code as the heading, I want to associate labels with the codes. For example, 100 is labeled as 'Runtime'.

Dustin

 

[dhookom]

Do you have a table that suggests that 100 is Runtime?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

I have a table that associates each code with a name, yes-- how do I use this in the report?

Thank you
Dusti

 

[dhookom]

Add the table that associates each code with a name to the query and set the name as the column heading for a crosstab.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

What is wrong with this query?

SELECT Sum(EMR_TotalGraphicTime.[Sum Of Time_Total]) AS [SUM], EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code
FROM EMR_EmployeeHistory INNER JOIN EMR_TotalGraphicTime ON EMR_EmployeeHistory.Time_ID = EMR_TotalGraphicTime.Time_ID
WHERE EMR_TotalGraphicTime.Time_Code In (100,900)
GROUP BY EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TotalGraphicTime.Time_Code
UNION SELECT Sum(EMR_TotalGraphicTime.[Sum Of Time_Total]) AS [SUM], EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, 0
FROM EMR_EmployeeHistory INNER JOIN EMR_TotalGraphicTime ON EMR_EmployeeHistory.Time_ID = EMR_TotalGraphicTime.Time_ID
WHERE EMR_TotalGraphicTime.Time_Code Not In (100,900)
GROUP BY EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder;
UNION SELECT EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder, EMR_TimeCodeList.Time_Code, EMR_TimeCodeList.Desc
FROM EMR_TimeCodeList INNER JOIN EMR_TotalGraphicTime ON EMR_TimeCodeList.Time_Code = EMR_EmployeeHistory.Time_Code
GROUP BY EMR_EmployeeHistory.Press_Operator, EMR_EmployeeHistory.SecondPressman, EMR_EmployeeHistory.Feeder;


Dustin

 

[dhookom]

I don't know what's wrong, you tell me! Are you getting an error message? Are you getting wrong results?

I thought I had suggested something about a crosstab a few posts back. What do you have for data and what do you want to do with it?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

I tried to add the table that associates the name with the code to the query, but it's telling me there is an error in the JOIN statement. What is the proper way to add the table to the query? Sorry!!

Dustin

 

[diddydustin]

The 'query design' selection is grayed out in my query so I can't just simply 'add' it.

Dustin

 

[dhookom]

I would save the Union query and then build a new query that is based on the union query and your other table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

Hey, you've been such a great help. However, I'm still very confused with the crosstab. I've got the queries working with the desc in the query.

Here's how I want the report to look:

Name1 Name2 Name3 Downtime Runtime Makeready
Dustin John Steve 0 3.3 2.1
Howard Dan Seth 4 5.4 0


You see what I'm getting at and that basically looks like a crosstab, however, I am confused on how to incorporate all queries into one, and if say Dustin, John, and Steve are in the Makeready query but in the Downtime, assign a 0 under that column.

Your help is very much appreciated!

Dustin

 

[dhookom]

What does your current raw (table) data look like and how do you want it to appear in your report? Try not to give me any queries, sql, etc. I want some basic table structures and sample records as well as how those sample records should look in your desired output.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

I have these tables:

EMR_EmployeeHistory
EMR_Graphic_TimeTable
EMR_TimeList


EMR_EmployeeHistory has:
Time_ID
Job_ID
Press_Operator
SecondPressman
Feeder
Shift
Date
Machine

EMR_Graphic_TimeTable has:
Time_Total
Time_Code
Time_ID

EMR_TimeList has:
Time_Code
Desc



I want a report that I can bring up by name (either Press_Operator, SecondPressman, or Feeder), date, shift, or machine that shows Downtime, Runtime, or Makeready time. Downtime is defined as not 100 (Time_Code) or 900, Runtime is defined as 100, and Makeready is defined as 900.


Thanks!!

Dustin

 

[dhookom]

I would normalize
EMR_EmployeeHistory has:
Time_ID
Job_ID
Press_Operator
SecondPressman
Feeder
Shift
Date
Machine
as
SELECT Time_ID, Job_ID, "Press Operator" as Task,
Shift, Press_Operator as Employee, Date, Machine
FROM EMR_EMployeeHistory
WHERE Press_Operator is not Null
UNION ALL
SELECT Time_ID, Job_ID, "Second Pressman",
Shift, SecondPressman, Date, Machine
FROM EMR_EMployeeHistory
WHERE SecondPressman is not Null
UNION ALL
SELECT Time_ID, Job_ID, "Feeder",
Shift, Feeder, Date, Machine
FROM EMR_EMployeeHistory
WHERE Feeder is not Null;

You can then create a crosstab or other query. You haven't stated "sample records as well as how those sample records should look in your desired output."



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

Okay, I got that query working. But now, when I do the crosstab query wizard, how do I set the times 'Makeready', 'Downtime', and 'Runtime' as the column headings while using the names, or shifts, or whatever on the left?


Thanks!!! I think we are so close
Dustin

 

[diddydustin]

Now my problem is basically adding the other time queries to this query...

Dustin

 

[dhookom]

Did you set the Column Heading to the [Desc] field? What is your crosstab SQL so far?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

No, all I have to use are the fields associated with the query you made. How can I add the Desc field and Time_Codes, etc? With the query you gave me it is not possible for me to do the 'query design' and simply add it. I am having a similiar problem where my query does not contain the time desc, code, or submitted times from the employees.

Dustin

 

[dhookom]

Save the union query that I suggested. Then create a new query based on the union query and your other tables.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.