Help joining data from two queries

[lmg0115]

I must preface this posting by saying that I am truly a novice at this, so any help you can provide is greatly appreciated. I have a query with 14 fields and a query with 13 fields. The first six fields of both have the same name. The others are different. I am trying to generate one query that I will use to create a report. When I do a select query based off of the individual tables it does not return all of the data. When I do two separate select queries, one for each table, I get all of the data. However, when I do a select query based off of the two individual queries it returns the data for the header query but has null and zero fields for the printer query. How can I join the two so that the data for both pieces of information on a given date are in one row which I can then come up with yield percentages and sums on the total line?

Qry1 Fields
Date, line, shift, CustName, WO, Assembly, HDRoP, HDRAB, HDR AvailHrs, HDRActHrs, HDRUptime, SleevesCut, HeadedTubes, HDRYield

Qry2 Fields
Date, line, shift, CustName, WO, Assembly, PRoP, PRAvailHrs, PRActHrs, PRUptime, TubesIn, ActTubes, PRYield


End Result
Date, line, shift, CustName, WO, Assembly, HDRoP, HDRAB, HDR AvailHrs, HDRActHrs, HDRUptime, Sleeves Cut, HeadedTubes, HDRYield, PRoP, PRAvailHrs, PRActHrs, PRUptime, TubesIn, ActTubes, PRYield, TotalLine AvailHrs, TotalLineActHrs, TotalLineUptime, TotalLineYield

 

[FoFa]

Have you clicked your join lines and set the proper join (all the records from tbl1 and those that match from tbl2) to create outer joins?

 

[lmg0115]

I tried that and it still didn't pull all of the records.

 

[khawar]

Field values in your tables must be different for those records which are not shown in the query
Joined query will show only those records which are same in both tables
To get all the records try using Left Join

 

[redneckgeek]

...unless BOTH tables contain unmatched records.

 

[khawar]

Ofcourse there are unmatched records in the table thats why query not returning all the records (or may be he is using some wrong join)

 

[redneckgeek]

If there are records in Qry1 that aren't in Qry2
AND
there are records in Qry2 that aren't in Qry1
and the poster wants to see ALL records from both queries, then an outer join won't help. A UNION is needed.

 

[khawar]

I think you didnt read properly he needs records in one row

 

[redneckgeek]

I think you didnt read properly he needs records in one row

I never said he couldn't have them in one row. I'm just putting all the options on the table, in case he hadn't thought of it.

Select field1, field2, sum(field3)
from (
     Select field1, field2, field3
     from qry1
     union all
     Select field1, field2, field3
     from qry2 ) rs
group by field1, field2

 

[lmg0115]

Thank you redneckgeek. This worked.