Join Properties to include all Records

[RAMMY987]

Hi,

i've got a query that is linking 2 tables.

i'm having problems with the join properties.

basically from what i can understand, there are 3 options.

1. Where join field from both tables are equal
2. All records from table 1 and only those from table 2 where they match
3. All records from table 2 and only those from table 1 where they match

how do i go about having all records from both tables showing?

 

[KenHigg]

Are the table fields identical? If so you should use a union query. If not, what are the common fields?

 

[RAMMY987]

Table 1

Field 1: Department
Field 2: Actual No. of People
Field 3: Actual Income

Table 2:

Field 1: Department
Field 2: Budget No. of People
Field 3: Budget Income

Both tables come from different data sources, otherwise i would just have one table

There are some Deaprtments (Field 1) in table 1 that aren't in table 2 and vice versa.

Bascially i want my report to have the follwoing columns

Department---Actual No. People---Budget No. People---Actual Income---Budget Income.

for all departments (Field 1) from both tables.

hope that makes sense

 

[Dennisk]

you need to link on department where the fields are equal.

 

[Rabbie]

You should be able to use a combination of Type 2 and 3 queries (as defined in your original post) together with a Union query to get the data for your report. You may need to use the DISTINCT ROW feature to make sure you don't get duplicate records where depts have entries in both tables.

You also need to decide how you handle entries where the dept is only in 1 or other of the tables.

Good luck.

 

[KenHigg]

This is just how I would do it, there may be a better way. I would create a dept table with one field 'dept' and make this field the primary key. Then append dept data from both tables into this dept table. You will get errors saying it can't append some rows because of duplicate values but that's fine. The create a query with this table and the other two tables and join from the dept table over to the other two table on the dept field.

Hope this makes sense...
You could do all of this dynamically but it'd be a pain to explain. If you want to post a sample database with the two tables I could try to whip you up something...

 

[Darren Bartrup]

I would use a UNION query:

SELECT Table1.Dept, Actual_People,Actual_Income, Budget_People, Budget_Income
FROM Table1 LEFT JOIN Table2 ON Table1.Dept = Table2.Dept

UNION  SELECT Table2.Dept, Actual_People,Actual_Income, Budget_People, Budget_Income
FROM Table2 LEFT JOIN Table1 ON Table2.Dept = Table1.Dept

ORDER BY Dept;

This link may help to (Full Outer Join): http://www.databasejournal.com/features/msaccess/article.php/3516561

 

[RAMMY987]

Hi Darren,

i followed the link and it help me tremendously.

thank you very much for that.

however, i now have another question. it may be better to refer to the example in the link you posted.

When the final query is produced there are 4 fields:

Last Name
Employees.City
Company Name
Suppliers.City

How would you bring it inline, so that there was just one field called City and the other fields were Last Name & Company Name

i appreciate some records will have blank last Name or Blank Company name, but i don't want a blank City

if you know what i mean

 

[Darren Bartrup]

Thought I'd use the Actual_People, Actual_Income for the answer as I still have the query on my computer.

So, to recap:

You have table 1 which holds Dept, Actual_People, Actual_Income:
Dept......................Actual_People...........Actual_Income
1.............................20............................£20.00
2.............................30............................£30.00
4.............................15............................£500.00

and table 2 which holds Dept, Budget_People, Budget_Income:
Dept....................Budget_People...............Budget_Income
1...................................5..............................£6.00
2...................................31............................£32.00
3...................................16............................£40.00

The query I used in my post above gives:
Dept......Actual_People.......Actual_Income...Budget_People....Budget_Income
1...............20....................£20.00....................5....................£6.00
2...............30....................£30.00....................31..................£32.00
3...................................................................16..................£40.00
4...............15....................£500.00

I'm assuming you want something like:
Dept..........People..........Income
1..................5...............£6.00
1..................20.............£20.00
2..................31.............£32.00
2..................30.............£30.00
3..................16.............£40.00
4..................15.............£500.00

Which would be:

SELECT Dept, Actual_People As People, Actual_Income As Income
From Query1
WHERE Actual_People Is Not Null Or Actual_Income Is Not Null

UNION ALL SELECT Dept, Budget_People, Budget_Income
FROM Query1
WHERE Budget_People Is Not Null Or Budget_Income Is Not Null

ORDER BY Dept ASC;

Hope that helps

 

[RAMMY987]

Thanks for your advice.

It's not quite like that.

it's more something like. i'll leave income out to save space in this post

Dept (Table 1)........Act People........Dept (Table 2)....... Bud people
1-----------------50---------------1----------------60
2-----------------30
3-----------------10---------------3----------------50
-----------------------------------4-----------------10
5-----------------73---------------5----------------65

I want that to show

Dept------------Act People------------Bud People
1------------------50-------------------60
2------------------30--------------------0
3------------------10-------------------50
4-------------------0-------------------10
5------------------73-------------------65

 

[Darren Bartrup]

It seems like you want the null values to show as 0's.

Will the addition of the NZ function to my original query help?

SELECT NZ(Table1.Dept,0) As Dept, NZ(Actual_People,0) As Act_People, NZ(Actual_Income,0) As Act_Income, NZ(Budget_People,0) As Bud_People, NZ(Budget_Income,0) As Bud_Income
FROM Table1 LEFT JOIN Table2 ON Table1.Dept = Table2.Dept

UNION SELECT NZ(Table2.Dept,0), NZ(Actual_People,0), NZ(Actual_Income,0), NZ(Budget_People,0), NZ(Budget_Income,0)
FROM Table2 LEFT JOIN Table1 ON Table2.Dept = Table1.Dept
ORDER BY Dept;