Comparison with multiple tables

HELP!!!!
I am using SQL in Access and need to pull all of the records that don't
match in the key field. The key fields are the same name in both tables and
I
have built a relationship on a different field. Both tables have some
matching records and some non matching. I want all of the records from both
tables that do not match!SELECT a.<column list>, b.<column list>
FROM a
FULL OUTER JOIN b
ON a.<primary key> = b.<primary key>
WHERE a.<primary key> IS NULL OR b.<primary key> IS NULL
Jacco Schalkwijk
SQL Server MVP
"Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
news:40EB8FA3-B1C8-412A-BC20-4E3E273EDD72@.microsoft.com...
> HELP!!!!
> I am using SQL in Access and need to pull all of the records that don't
> match in the key field. The key fields are the same name in both tables
> and I
> have built a relationship on a different field. Both tables have some
> matching records and some non matching. I want all of the records from
> both
> tables that do not match!|||Try,
select *
from t1 full outer join t2
on t1.pk_col = t2.pk_col
where t1.pk_col is null or t2.pk_col is null
AMB
"Tess9126" wrote:

> HELP!!!!
> I am using SQL in Access and need to pull all of the records that don't
> match in the key field. The key fields are the same name in both tables an
d I
> have built a relationship on a different field. Both tables have some
> matching records and some non matching. I want all of the records from bot
h
> tables that do not match!|||Access did not like the word outer. I am at work with absolutely no referenc
e
material. I am familure with SQL but I am really stuck. I want to thank you
for trying to help me!
"Jacco Schalkwijk" wrote:

> SELECT a.<column list>, b.<column list>
> FROM a
> FULL OUTER JOIN b
> ON a.<primary key> = b.<primary key>
> WHERE a.<primary key> IS NULL OR b.<primary key> IS NULL
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
> news:40EB8FA3-B1C8-412A-BC20-4E3E273EDD72@.microsoft.com...
>
>|||Ah, Access. My Access SQL is rather rusty, so maybe the following will work:
SELECT a.<column list>, b.<column list>
FROM a
LEFT OUTER JOIN b
ON a.<primary key> = b.<primary key>
WHERE b.<primary key> IS NULL
UNION
SELECT a.<column list>, b.<column list>
FROM b
LEFT OUTER JOIN a
ON b.<primary key> = a.<primary key>
WHERE a.<primary key> IS NULL
If it doesn't work, the best thing to do is post your question on an Access
newsgroup.
Jacco Schalkwijk
SQL Server MVP
"Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
news:43F5BF5D-9B8C-401C-B1F7-136381102C7C@.microsoft.com...
> Access did not like the word outer. I am at work with absolutely no
> reference
> material. I am familure with SQL but I am really stuck. I want to thank
> you
> for trying to help me!
> "Jacco Schalkwijk" wrote:
>|||Then you will need a union all of two outer joins.
select t1.c1, ..., t1.cn
from t1 left join t2 on t1.pk_col = t2.pk_col
where t2.pk_col is null
union all
select t2.c1, ..., t2.cn
from t1 right join t2 on t1.pk_col = t2.pk_col
where t1.pk_col is null
AMB
"Tess9126" wrote:
> Access did not like the word outer. I am at work with absolutely no refere
nce
> material. I am familure with SQL but I am really stuck. I want to thank yo
u
> for trying to help me!
> "Jacco Schalkwijk" wrote:
>