List items on 1 table NOT in another

[mommio2]

Hello, I am desperate to find out how to do this either with SQL or in
design view in Access 2003. Any help you could give me would be REALLY
appreciated!

I have 2 tables, Customer & Order.

Customer has a CustId
Order has an OrderId and a CustId

If a customer has placed an order at any time, he/she will be listed in the
CustID field in the order table, thus he may be in there multiple times.

I need to find the SQL (or a way in design view in Access 2003) to list all
customers who are NOT in the Order table.

THANKS!!!
Mommio2

 

[Tom van Stiphout]

On Wed, 22 Jul 2009 10:02:10 -0400, "mommio2"

select * from Customers
where CustID not in (select CustID from Orders)
Or you can use an outer join.

-Tom.
Microsoft Access MVP

 

[Roger Carlson]

Do an Outer Join (Left Join or Right Join).

In the Query Builder, right-click the Join line and select either option 2
or 3, whichever makes the arrow point toward the Order table. Make sure you
add the CustID from from both the Customer table and the Orders table in the
query and use IS NULL in the criteria of the Order table field.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Spencer]

There is the unmatched query wizard that will build a query to find records
that are unmatched. It should work very well with your two tables.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County