Join Properties - Show all records

[Clive]

I've got a query that is linking 2 tables. However, I'm having problems with
the join properties in linking these tables.



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?



The tables are as follows:

Table 1

Field 1: Department

Field 2: Actual Numbers

Field 3: Actual Income



Table 2:

Field 1: Department

Field 2: Budget Numbers

Field 3: Budget Income



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

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

Basically I want my report to have the following columns:



Department---Actual Numbers---Budget Numbers---Actual Income---Budget Income.



For all Departments (Field 1) from both tables.

Any ideas would be very much appreciated

 

[Pieter Wijnen]

You can use a union query

SELECT A.*, B.* FROM TABLE1 A INNER JOIN TABLE2 B ON (A.FIELD1 = B.FIELD2)
UNION ALL
SELECT A.*, B.* FROM TABLE1 A LEFT JOIN TABLE2 B ON (A.FIELD1 = B.FIELD2)
WHERE B.FIELD2 IS NULL
UNION ALL
SELECT A.*, B.* FROM TABLE1 A RIGHT JOIN TABLE2 B ON (A.FIELD1 = B.FIELD2)
WHERE A.FIELD1 IS NULL

HtH

Pieter

 

[Dirk Goldgar]

I've got a query that is linking 2 tables. However, I'm having problems
with
the join properties in linking these tables.


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?

The tables are as follows:

Table 1
Field 1: Department
Field 2: Actual Numbers
Field 3: Actual Income

Table 2:
Field 1: Department
Field 2: Budget Numbers
Field 3: Budget Income

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

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

Basically I want my report to have the following columns:

Department---Actual Numbers---Budget Numbers---Actual Income---Budget
Income.

For all Departments (Field 1) from both tables.

Any ideas would be very much appreciated

The Jet database engine doesn't support full outer joins, so you have to
combine a left join with a right join via a union query. The result will
not be updatable, but if you just want it for a report that should be fine.
Try something like this:

SELECT
[Table 1].Department,
[Actual Numbers],
[Budget Numbers],
[Actual Income],
[Budget Income]
FROM [Table 1] LEFT JOIN [Table 2]
ON [Table 1.Department] = [Table 2.Department]
UNION ALL
SELECT
[Table 2].Department,
[Actual Numbers],
[Budget Numbers],
[Actual Income],
[Budget Income]
FROM [Table 1] RIGHT JOIN [Table 2]
ON [Table 1.Department] = [Table 2.Department]
WHERE [Table 1].Department Is Null

 

[Clive]

Thanks Dirk,

This has got me the furthest so far. However, i'm getting duplicate values
in the budget. i think i may need to put a max expression against the budget
figures.

is it just a case of putting

Max[Budget Numbers],

Thanks in advance

Clive

I've got a query that is linking 2 tables. However, I'm having problems
with
the join properties in linking these tables.


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?

The tables are as follows:

Table 1
Field 1: Department
Field 2: Actual Numbers
Field 3: Actual Income

Table 2:
Field 1: Department
Field 2: Budget Numbers
Field 3: Budget Income

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

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

Basically I want my report to have the following columns:

Department---Actual Numbers---Budget Numbers---Actual Income---Budget
Income.

For all Departments (Field 1) from both tables.

Any ideas would be very much appreciated

The Jet database engine doesn't support full outer joins, so you have to
combine a left join with a right join via a union query. The result will
not be updatable, but if you just want it for a report that should be fine.
Try something like this:

SELECT
[Table 1].Department,
[Actual Numbers],
[Budget Numbers],
[Actual Income],
[Budget Income]
FROM [Table 1] LEFT JOIN [Table 2]
ON [Table 1.Department] = [Table 2.Department]
UNION ALL
SELECT
[Table 2].Department,
[Actual Numbers],
[Budget Numbers],
[Actual Income],
[Budget Income]
FROM [Table 1] RIGHT JOIN [Table 2]
ON [Table 1.Department] = [Table 2.Department]
WHERE [Table 1].Department Is Null

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)