CODEWITHSUNDEEP
ms-accessms-access-2003
user1477681
user1477681

Reputation: 31

Combine three tables in MS Access 2003

I have three queries in Access 2003 which I need to combine in the one query. Not all records exist on all three tables so I suppose I want something like a full outer join.

Query 1 Fields: Record number, surname, firstname, subcentre

Query 2 Fields: Record number, surname, firstname, case worker

Query 3 Fields: Record number, surname, firstname, doctor

I need to be able to display:

Record number, surname, firstname, case worker, doctor, subcentre

However, at the moment I can workout how to use a left join then union with a right join between two queries but don't know how to extend this to three queries.

Any ideas/suggestions?

Upvotes: 3

Views: 2477

Answers (1)

Fionnuala
Fionnuala

Reputation: 91316

How about:

SELECT a.[Record number], a.surname, a.firstname, 
       t1.subcentre, t2.[case worker], t3.doctor
FROM
(((SELECT [Record number], surname, firstname FROM T1
UNION
SELECT [Record number], surname, firstname FROM T2
UNION
SELECT [Record number], surname, firstname FROM T3) As A
LEFT JOIN T1 ON A.[Record number]=T1.[Record number])
LEFT JOIN T2 ON A.[Record number]=T2.[Record number])
LEFT JOIN T3 ON A.[Record number]=T3.[Record number]

The use of UNION rather than UNION ALL in the query creates a unique list of [Record number], surname, first name taken from all three tables. The derived table can then be used to LEFT JOIN to the other three tables. LEFT JOIN ensures that all records from the derived tables are included.

As usual with SQL, there are other, similar ways to do this. The above would be slow on a very large file, but unless you have something other than a Jet/ACE back-end, it is unlikely that you will notice any delay.

With, say, an SQL Server back-end, you would use a pass-through query and t-sql syntax. With MySQL back-end, I think you are obliged to use a passthrough query when you go above the one UNION statement.

Upvotes: 3

Related Questions