Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
URGENT: Query to view data from table A minus table B.
Hi, I would like to make a query that will give me rows from table A excluding the rows in table B. Both tables have the same structure.
I know I can use something like below, but I need to do a join on each field (I have 20 fields). The problem I am having is that some of these fields may be null on various rows, so a comparison of A.field1 = B.field1 does not work.
Example:
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (Table2.d = Table1.d) AND (Table2.c =
Table1.c) AND (Table2.b = Table1.b) AND (Table2.a = Table1.a) AND
(Table2.Field1= Table1.Field1)
WHERE (((Table1.Field1) Is Null));
I am using MS Access 2000, and can use VBA to help do this if necessary - but I would like to avoid using Access specific commands/features since I would like to migrate to another system in the near future. Can someone provide me a fairly simple solution.
Thanks.
I know I can use something like below, but I need to do a join on each field (I have 20 fields). The problem I am having is that some of these fields may be null on various rows, so a comparison of A.field1 = B.field1 does not work.
Example:
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (Table2.d = Table1.d) AND (Table2.c =
Table1.c) AND (Table2.b = Table1.b) AND (Table2.a = Table1.a) AND
(Table2.Field1= Table1.Field1)
WHERE (((Table1.Field1) Is Null));
I am using MS Access 2000, and can use VBA to help do this if necessary - but I would like to avoid using Access specific commands/features since I would like to migrate to another system in the near future. Can someone provide me a fairly simple solution.
Thanks.
Asked:
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Wrap the comparison values in the "nz" function:
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (nz(Table2.d) = nz(Table1.d)) AND (nz(Table2.c) =
nz(Table1.c)) AND (nz(Table2.b) = nz(Table1.b)) AND (nz(Table2.a) = nz(Table1.a)) AND
(nz(Table2.Field1)= nz(Table1.Field1))
WHERE (Table1.Field1 Is Null);