comparing data in 2 tables

[l0knezmonstir]

I have 2 tables that I am trying to create and set up 1 to many referential integrity, by linking ID numbers. The problem is that not all the ids in table2 have a id in table1. How can I find out which ids in table2 do not have an id in table1?

-Marty Loftus

 

[gol4]

1.do a left join and look for nulls
or
2. select id from tbl1 where tbl1.id not(select id from tbl2)
or
3.have access build a find unmatched query for you using the wizard

 

[BlackKnight]

Hi,

Try this example...the query returns the customerid that are in tblOrder but not in tblCustomer.

SELECT tblOrder.CustomerID
FROM tblCustomer RIGHT JOIN tblOrder ON tblCustomer.CustomerID = tblOrder.CustomerID
GROUP BY tblOrder.CustomerID, tblCustomer.CustomerID
HAVING (((tblCustomer.CustomerID) Is Null))
ORDER BY tblOrder.CustomerID, tblCustomer.CustomerID;

Have a good one!
BK

 

[l0knezmonstir]

Worked Like a Charm. Thanks.

-Marty Loftus