Comparing Lists

B

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.
 
J

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
 
B

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.
 
B

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top