Join Queries

[lmg0115]

I have the following two queries that I generated from the results of the union query below.
I would now like to join the two queries so that the resultant query results look like this:

Date, Line, Shift, Work Order, HeaderOP, HeaderAB, HeaderAvailHrs, SleevesCut, HeadedTubes, Printer Operator, Printer Avail Hrs, Tubes Into Press.

qryTwoNR
SELECT TestHDRPRUnionqryforProdRpt.Date, TestHDRPRUnionqryforProdRpt.Line, TestHDRPRUnionqryforProdRpt.Shift, TestHDRPRUnionqryforProdRpt.[Work Order], [tbl_Employees]![Last_Name] & " " & [tbl_Employees]![First_Name] AS [Printer Operator ID], TestHDRPRUnionqryforProdRpt.MyField, TestHDRPRUnionqryforProdRpt.[Printer Avail Hrs], TestHDRPRUnionqryforProdRpt.[Tubes Into Press]
FROM TestHDRPRUnionqryforProdRpt INNER JOIN tbl_Employees ON TestHDRPRUnionqryforProdRpt.[Printer Operator ID] = tbl_Employees.EMPNO
GROUP BY TestHDRPRUnionqryforProdRpt.Date, TestHDRPRUnionqryforProdRpt.Line, TestHDRPRUnionqryforProdRpt.Shift, TestHDRPRUnionqryforProdRpt.[Work Order], [tbl_Employees]![Last_Name] & " " & [tbl_Employees]![First_Name], TestHDRPRUnionqryforProdRpt.MyField, TestHDRPRUnionqryforProdRpt.[Printer Avail Hrs], TestHDRPRUnionqryforProdRpt.[Tubes Into Press]
HAVING (((TestHDRPRUnionqryforProdRpt.MyField)="none"));


qryThreeNR
SELECT TestHDRPRUnionqryforProdRpt.Date, TestHDRPRUnionqryforProdRpt.Line, TestHDRPRUnionqryforProdRpt.Shift, TestHDRPRUnionqryforProdRpt.[Work Order], [tbl_Employees]![Last_Name] & " " & [tbl_Employees]![First_Name] AS HeaderOP, TestHDRPRUnionqryforProdRpt.MyField AS HeaderAB, TestHDRPRUnionqryforProdRpt.[Printer Avail Hrs] AS HeaderAvailHrs, TestHDRPRUnionqryforProdRpt.[Tubes Into Press] AS SleevesCut, TestHDRPRUnionqryforProdRpt.MyField1 AS HeadedTubes
FROM TestHDRPRUnionqryforProdRpt INNER JOIN tbl_Employees ON TestHDRPRUnionqryforProdRpt.[Printer Operator ID] = tbl_Employees.EMPNO
GROUP BY TestHDRPRUnionqryforProdRpt.Date, TestHDRPRUnionqryforProdRpt.Line, TestHDRPRUnionqryforProdRpt.Shift, TestHDRPRUnionqryforProdRpt.[Work Order], [tbl_Employees]![Last_Name] & " " & [tbl_Employees]![First_Name], TestHDRPRUnionqryforProdRpt.MyField, TestHDRPRUnionqryforProdRpt.[Printer Avail Hrs], TestHDRPRUnionqryforProdRpt.[Tubes Into Press], TestHDRPRUnionqryforProdRpt.MyField1
HAVING (((TestHDRPRUnionqryforProdRpt.MyField)="A" Or (TestHDRPRUnionqryforProdRpt.MyField)="B"));

Union Query
SELECT [Printer Information].[Date], [Printer Information].[Line], [Printer Information].[Shift], [Printer Information].[Work Order], [Printer Information].[Printer Operator ID], "none" As MyField, [Printer Information].[Printer Avail Hrs], [Printer Information].[Tubes Into Press], "none" As MyField1
FROM [Printer Information] LEFT JOIN [Header Information]
ON [Printer Information].[Date] = [Header Information].[Date]

UNION SELECT [Header Information].[Date], [Header Information].[Line], [Header Information].[Shift], [Header Information].[Work Order], [Header Information].[Header Operator ID],[Header Information].[HeaderAB], [Header Information].[Header Avail Hrs], [Header Information].[Sleeves Cut],[Header Information].[Headed Tubes]
FROM [Header Information] RIGHT JOIN [Printer Information]
ON [Printer Information].[Date] = [Header Information].[Date];

How do I make this happen? I have tried different joins and can’t get the correct results. What am I doing wrong? Here’s my code:

SELECT qryTwoNR.Date, qryTwoNR.Line, qryTwoNR.Shift, qryTwoNR.[Work Order], qryTwoNR.[Printer Operator ID], qryTwoNR.[Printer Avail Hrs], qryTwoNR.[Tubes Into Press], qryThreeNR.HeaderOP, qryThreeNR.HeaderAB, qryThreeNR.HeaderAvailHrs, qryThreeNR.SleevesCut, qryThreeNR.HeadedTubes
FROM qryTwoNR INNER Join qryThreeNR ON qryTwoNR.Line = qryThreeNR.Line
ORDER BY qryTwoNR.Date;

This give me 800,000 records when I am looking for 210,000 records.

 

[Banana]

That's quite a lot of data to digest and I'll have to ask to explain a bit more.

Why do we need qtrNRTwo and qtrNRThree? They seems to be identical except for the criteria, which we can just add a 'Or', no? This also would eliminate the need to UNION them...

But in your final query, you're selecting some from qtrNRTwo and some from qtrNRThree... Can you please explain the logic behind that?

An overview would go long way in helping us why those steps are necessary and what problems you're facing.

HTH.

 

[lmg0115]

Sorry for the lengthyness of my previous message. Here is what I am trying to achieve: I want to create a report based off of data from multiple tables, but the main two tables are, header and printer. These tables both have one to many relationships with other tables but are not related themselves (which they should be, but that is another story), hence the union query. The union query gave me all of the records for each table but joined them into one column, which I expected. For example Printer Operator and Header Operator are in the same column. I now need to have the records appear in a single row whose fields would look like this: Date, Line, Shift, Workorder, Header Operator, Header Available hours, Printer Operator, Printer Available Hours, etc. From what I read in some of the other threads I need three queries to make this happen but cant seem to make the third one work. Is there a way to do this in one query based off of the Union query?

 

[Banana]

That goes a long way toward making sense of it all.

The issue is that UNION query has to have same number of columns, but you want to expose columns from both tables with different names. So this is not appropriate in this scenario.

Rather, what you want to do is create a query pulling all records from each table with the criteria you supply. You would then add the missing column in each query with a ZLS. For example, if a query is pulling from Header table:

SELECT [Header Information], "" AS [Printer Information] FROM tblPrinter;

Then you create a third query that then UNIONs the result of two query.

Did that make sense?