Query two tables and return all data from both tables

[erict]

I have 2 tables (created by queries to linked tables) with the following fields:
(1) Project, Project Name, Dept, Actual Hours, Forecast Hours, Period Ending (2) Project, Project Name, Dept, Original Budget Hours, Total CN Hours, Total Approved Hours
Trying to generate a query which combines data from both tables, but both tables do not have data for each row. For example, table (1) does not always have “Actual Hrs” data for each “Dept” and table (2) does not always have “Orig Budget Hrs” for each “Dept.
I’ve really tried all I know to get this to work and can’t get it. Thank you for your help.
The table below is what the results should look like.

Project​

Project Name​

Dept​

Original Budget Hours​

Total CN Hours​

Total Approved Hours​

Actual Hours​

Forecast Hours​

0123456
Test Project
11

154 ​

10 ​

0123456
Test Project
12

147 ​

68 ​

215 ​

170 ​

5 ​

0123456
Test Project
16

​

​

​

13 ​

0 ​

0123456
Test Project
18

291 ​

56 ​

347 ​

643 ​

10 ​

0123456
Test Project
22

60 ​

4 ​

64 ​

​

​

0123456
Test Project
26

479 ​

180 ​

659 ​

783 ​

0 ​

0123456
Test Project
32

60 ​

34 ​

94 ​

216 ​

0 ​

0123456
Test Project
34

​

​

​

16 ​

15 ​

0123456:banghead:
Test Project
36

10 ​

0 ​

10 ​

​

​

 

[Ranman256]

if data is mssing on key fields to join, then you cant do it.
unless you want to add all data from table1 to a temp table, and then add all data from table2
sum to get 1 data set.

or
create a union query to pull data from both into 1 single query.

 

[KenHigg]

Seems like a union query is what you need -

Edit: Beat me to the punch...

 

[erict]

Thank you - got it to work. Another problem crept up on me. The data pulled from the (2) different tables within the union query are on different rows even though the first three fields are the same. Is there a function to get data on same row.
Thank you again for your time.

 

[KenHigg]

Sounds like you need to group on these composite key fields in a second query, concatenating the other fields. ?

 

[Ranman256]

If they have different columns, in the union query, create Fake columns to compensate in the table where they don't exist.

 

[erict]

Got it to work. Created second query utilizing Union query results - with function Sum(Val([fieldname])). Thank you