Comparing Lists

[Bill]

Hi All,

Can anyone help with this please.

I have two lists, LIST1 and LIST2. They are in separate tables also called
LIST1 and LIST2 and each table has a unique reference text field called REF.

The lists are quite lengthy and to do this manually would be involved!

I need to establish what REF fields are in LIST1 and LIST2.

What fields are in LIST1 but not in LIST2

What fields are in LIST2 but not in LIST1

I can't get my head round it. Can anyone help please.

Ta.
Bill.

 

[John Spencer]

Three queries combined into a UNION Query

SELECT List1.Ref, "Both" as InTable
FROM List1 INNER JOIN List2
On List1.Ref = List2.Ref
UNION ALL
SELECT List1.Ref, "List1" as InTable
FROM List1 LEFT JOIN List2
On List1.Ref = List2.Ref
WHERE List2.Ref is Null
UNION ALL
SELECT List2.Ref, "List2" as InTable
FROM List1 RightJOIN List2
On List1.Ref = List2.Ref
WHERE List1.Ref is Null


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Bill]

Wow, many thanks.
I will try that later.
Would have taken me forever!!!
Bill.

 

[Bill]

John,

Sorry!

How do I do this in Access 2000?

I have tried opening a new blank query and pasting your SQL into the SQL
View and I get Syntax Error in From Clause.

Regards.
Bill.

 

[Bill]

So'ok, spotted it.
There was no space in RightJOIN.
Works now and its far better than anything I would have done in a million
years!!!!
Thanks again.
Bill.