By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

How do you make a query return values even if they are null? "Is Null" won't work.

100+
P: 256
I have perused the web for the answer to this and I almost think it's so elementary that no one has ever had to ask it. Haha. (FYI: I am not afraid of looking dumb.) :-)

I have a simple query that links a customer number(tblHOTSTICK_CUSTOMERS) to their account number (like a charge account number to pay) (tblHOTSTICK_ACCOUNT). At times I want to go through and see who doesn't have an account number, but I can't get my query to show them if the account values are null. I have tried the simple things like making "criteria" "Is Null." I don't write queries with code like most of you smart people. :-) I use the design tools in MS Access 2007 (open the query, click design view). I have tblHOTSTICK_CUSTOMERS joined (one to one) to tblHOTSTICK_ACCOUNT. How can I get it to show EVERY customer number in tblHOTSTICK_CUSTOMERS even if there is no customer account number data in tblHOTSTICK_ACCOUNT? (FYI This may not be just a query problem. The customer number doesn't show up in tblHOSTICK_ACCOUNT either if it doesn't have an account number (which is as expected I suppose). However if I dirty the form and delete the data back out, it WILL show up.)

I am posting the sql for my query. Please note this if for you to help me only. I don't write this stuff. I prefer to work in design view. If you can help me that way I would understand it better.

PS. All the "wierd" field names are parts of our internal accounting numbers (PRCN, RT, etc).

Expand|Select|Wrap|Line Numbers
  1. SELECT HOTSTICK_CUSTOMERS.CUST_NUM
  2.      , HOTSTICK_ACCOUNT.PRCN
  3.      , HOTSTICK_ACCOUNT.RT
  4.      , HOTSTICK_ACCOUNT.ACTIVITY
  5.      , HOTSTICK_ACCOUNT.EWO
  6.      , HOTSTICK_ACCOUNT.PROJECT
  7.      , HOTSTICK_ACCOUNT.LOCATION
  8.      , HOTSTICK_ACCOUNT.FERC
  9.      , HOTSTICK_ACCOUNT.SUB
  10.      , HOTSTICK_ACCOUNT.RORG
  11. FROM   HOTSTICK_CUSTOMERS
  12.        INNER JOIN
  13.        HOTSTICK_ACCOUNT
  14.   ON   HOTSTICK_CUSTOMERS.CUST_NUM = HOTSTICK_ACCOUNT.CUST_NUM;
I'm headed home to clear my head. I'll check back in the morning. :-)
Sep 4 '12 #1

✓ answered by Rabbit

An inner join, like the one you have, must always match. What you are looking for is an left or right outer join.
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tableA
  3. LEFT JOIN tableB
  4. ON tableA.key = tableB.key
This gives you everything in tableA regardless of whether or not a match is found in tableB. A right join will give you the reverse.

Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,366
An inner join, like the one you have, must always match. What you are looking for is an left or right outer join.
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tableA
  3. LEFT JOIN tableB
  4. ON tableA.key = tableB.key
This gives you everything in tableA regardless of whether or not a match is found in tableB. A right join will give you the reverse.
Sep 4 '12 #2

NeoPa
Expert Mod 15k+
P: 31,491
Rabbit's quite right Danica. Your query SQL would look something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT HOTSTICK_CUSTOMERS.CUST_NUM
  2.      , HOTSTICK_ACCOUNT.PRCN
  3.      , HOTSTICK_ACCOUNT.RT
  4.      , HOTSTICK_ACCOUNT.ACTIVITY
  5.      , HOTSTICK_ACCOUNT.EWO
  6.      , HOTSTICK_ACCOUNT.PROJECT
  7.      , HOTSTICK_ACCOUNT.LOCATION
  8.      , HOTSTICK_ACCOUNT.FERC
  9.      , HOTSTICK_ACCOUNT.SUB
  10.      , HOTSTICK_ACCOUNT.RORG
  11. FROM   HOTSTICK_CUSTOMERS
  12.        LEFT JOIN
  13.        HOTSTICK_ACCOUNT
  14.   ON   HOTSTICK_CUSTOMERS.CUST_NUM = HOTSTICK_ACCOUNT.CUST_NUM
In the design view of the query double-click on the line joining the two tables and select option 2 (or 3 if you have it in reverse).
Sep 5 '12 #3

100+
P: 256
THAT'S IT!!!!!!
Thanks.
Happy again! :-)
Sep 5 '12 #4

NeoPa
Expert Mod 15k+
P: 31,491
A happy Danica is always good :-)
Sep 7 '12 #5

100+
P: 256
Danica could not agree more! :-D
Sep 7 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.