Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 733
  • Last Modified:

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.
0
shamstar
Asked:
shamstar
1 Solution
 
jrb1Commented:
When you do your join in the above query, you can use the nz function (in access, but there is an equivalent in sql server and oracle) to convert the null value.

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);
0
 
shamstarAuthor Commented:
Thats fantastic, exactly the type of answer I needed.

Thanks for your help jrb1!
0
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

Featured Post

Easily Design & Build Your Next Website

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now