Full Join in MS ACCESS 2007?

[mls via AccessMonster.com]

I need to combine two tables and get all the PID_NUMBER. Say table 1 has 25
rows of PID's and Table2 has 50 PID's, and I want to combine both tables and
my result should show 75 rows.
Is this possible?

I tried the following query but it's not working..

SELECT PID_NUMBER
FROM [table1] FULL JOIN [Table2] ON [Table1].PID_NUMBER = [Table2].
PID_NUMBER;

Thank you

 

[Marshall Barton]

I need to combine two tables and get all the PID_NUMBER. Say table 1 has 25
rows of PID's and Table2 has 50 PID's, and I want to combine both tables and
my result should show 75 rows.
Is this possible?

I tried the following query but it's not working..

SELECT PID_NUMBER
FROM [table1] FULL JOIN [Table2] ON [Table1].PID_NUMBER = [Table2].
PID_NUMBER;

A full outer join would look like:

SELECT PID_NUMBER
FROM [table1] LEFT JOIN [Table2]
ON [Table1].PID_NUMBER = [Table2].PID_NUMBER
UNION ALL
SELECT PID_NUMBER
FROM [table1] RIGHT JOIN [Table2]
ON [Table1].PID_NUMBER = [Table2].PID_NUMBER
WHERE [Table1].PID_NUMBER Is Null

But, if you expect to get 75 records in the result, then you
do not have any records with the same pid in both tables.
If that's the case then you can just use:

SELECT PID_NUMBER
FROM [table1]
UNION ALL
SELECT PID_NUMBER
FROM [Table2]

 

[Tom Lake]

I need to combine two tables and get all the PID_NUMBER. Say table 1 has 25
rows of PID's and Table2 has 50 PID's, and I want to combine both tables and
my result should show 75 rows.
Is this possible?

I tried the following query but it's not working..

SELECT PID_NUMBER
FROM [table1] FULL JOIN [Table2] ON [Table1].PID_NUMBER = [Table2].
PID_NUMBER;

Make a query that includes both tables but has no connection between them at all.

Here are two tables for example:

SELECT [Employee Table].[Employee Number], [Fax Table].[Date Entered], [Fax Table].To FROM
[Employee Table], [Fax Table];

Tom Lake

 

[mls via AccessMonster.com]

Thank you very much this works perfectly.

I need to combine two tables and get all the PID_NUMBER. Say table 1 has 25
rows of PID's and Table2 has 50 PID's, and I want to combine both tables and

[quoted text clipped - 6 lines]

FROM [table1] FULL JOIN [Table2] ON [Table1].PID_NUMBER = [Table2].
PID_NUMBER;

A full outer join would look like:

SELECT PID_NUMBER
FROM [table1] LEFT JOIN [Table2]
ON [Table1].PID_NUMBER = [Table2].PID_NUMBER
UNION ALL
SELECT PID_NUMBER
FROM [table1] RIGHT JOIN [Table2]
ON [Table1].PID_NUMBER = [Table2].PID_NUMBER
WHERE [Table1].PID_NUMBER Is Null

But, if you expect to get 75 records in the result, then you
do not have any records with the same pid in both tables.
If that's the case then you can just use:

SELECT PID_NUMBER
FROM [table1]
UNION ALL
SELECT PID_NUMBER
FROM [Table2]

 

[Tom van Stiphout]

No, that is a carthesian product, which would produce 25 * 50 rows.

-Tom.
Microsoft Access MVP

I need to combine two tables and get all the PID_NUMBER. Say table 1 has 25
rows of PID's and Table2 has 50 PID's, and I want to combine both tables and
my result should show 75 rows.
Is this possible?

I tried the following query but it's not working..

SELECT PID_NUMBER
FROM [table1] FULL JOIN [Table2] ON [Table1].PID_NUMBER = [Table2].
PID_NUMBER;

Make a query that includes both tables but has no connection between them at all.

Here are two tables for example:

SELECT [Employee Table].[Employee Number], [Fax Table].[Date Entered], [Fax Table].To FROM
[Employee Table], [Fax Table];

Tom Lake

 

[mls via AccessMonster.com]

I am trying to create table3 with these union all results? and it says Action
query cannot be used as row source cannot create

SELECT PID_NUMBER into table3
FROM [table1]
UNION ALL
SELECT PID_NUMBER
FROM [Table2]

any thoughts?

Thank you very much this works perfectly.
[quoted text clipped - 22 lines]

SELECT PID_NUMBER
FROM [Table2]

 

[Douglas J. Steele]

Why would you want to store the data redundantly? Simply use the Union query
whenever you need the data.

If you MUST store it, it would be

SELECT PID_NUMBER INTO Table3
FROM (
SELECT PID_NUMBER
FROM [table1]
UNION ALL
SELECT PID_NUMBER
FROM [Table2]
) AS Subq


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I am trying to create table3 with these union all results? and it says
Action
query cannot be used as row source cannot create

SELECT PID_NUMBER into table3
FROM [table1]
UNION ALL
SELECT PID_NUMBER
FROM [Table2]

any thoughts?

Thank you very much this works perfectly.

I need to combine two tables and get all the PID_NUMBER. Say table 1 has
25
rows of PID's and Table2 has 50 PID's, and I want to combine both tables
and

[quoted text clipped - 22 lines]

SELECT PID_NUMBER
FROM [Table2]

 

[mls via AccessMonster.com]

Thank you that helps. I want to compare the 2 table and assign the testNo.
Table1 contains last years data and table2 contains this years data. If the
patient comes back for check up I need to assign 'test 2' for his testno

Why would you want to store the data redundantly? Simply use the Union query
whenever you need the data.

If you MUST store it, it would be

SELECT PID_NUMBER INTO Table3
FROM (
SELECT PID_NUMBER
FROM [table1]
UNION ALL
SELECT PID_NUMBER
FROM [Table2]
) AS Subq

I am trying to create table3 with these union all results? and it says
Action

[quoted text clipped - 17 lines]

SELECT PID_NUMBER
FROM [Table2]