Avatar of Amour22015
Amour22015
 asked on

MS Access Query - Using Nulls

Hi Experts,

When I use this:
WHERE (((USER_dbo_ams_contact.orgName) Is Not Null) AND ((USER_dbo_ams_contact.firstName) Is Not Null) AND ((USER_dbo_ams_contact.lastName) Is Not Null));

Open in new window


I get way less than what I should have.

When I do this:
WHERE (((USER_dbo_ams_contact.orgName) Is Null) AND ((USER_dbo_ams_contact.firstName) Is Null) AND ((USER_dbo_ams_contact.lastName) Is Null));

Open in new window


I get only 14 records

So on the first snippet (ID=8272141) I am not getting the correct number it should only be 14 less.

Please help and thanks.
DatabasesMicrosoft AccessSQL

Avatar of undefined
Last Comment
Pavel Celba

8/22/2022 - Mon
Rey Obrero (Capricorn1)

why do you think the query is not giving the correct records?
- your first query will return all records that have fields orgName, firstName and lastName filled with values
orgName      firstName          lastName
A                    john                    doe                     << satisfy the where condition
                       john                    doe                    << does not satisfy the where condition

what do you think?
Dale Fye

My guess is that in your first query, you want to use OR instead of AND, try this query:

SELECT IsNull(USER_dbo_ams_contact.orgName) as OrgNameNULL
, IsNull(USER_dbo_ams_contact.firstName) as FirstNameNULL
, IsNull(USER_dbo_ams_contact.lastName) as LastNameNull
, Count(*) as RecCount
FROM yourTable
GROUP BY IsNull(USER_dbo_ams_contact.orgName)
, IsNull(USER_dbo_ams_contact.firstName)
, IsNull(USER_dbo_ams_contact.lastName)

This will give you a series of records which identify the number of records where each of those fields is null, it could look something like

OrgNameNull     FirstNameNull      LastNameNull    RecCount
    0                               0                                 0                      25
    1                               0                                 0                      15
    0                               1                                 0                         X
    1                               1                                 0                        Y
    1                                0                                1                        Z
    1                               1                                 1                        A

With this, you will be able to determine why the two queries do not appear to have the correct number of records.
Amour22015

ASKER
But yet when I run the second query it gives me only 14 record where all 3 conditions are null.  So if I do the reverse should it give me 14 records less?

If I don't use the Where clause I get:
12,433
If I put in the where clause I get:
11,460

Should I not get:
12,433
-       14
12,421

Please help and thanks....
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

<Should I not get:
12,433
-       14
12,421 >

NOT necessarily..

your second query will return all records where the fields orgName, firstName and lastName have NO values

orgName      firstName          lastName
NULL             NULL                   NULL                     << satisfy the where condition
                       john                    doe                    << does not satisfy the where condition
Amour22015

ASKER
Great thanks
ste5an

First of all: Use table alias names.

Then post complete examples.

so you're trying to compare:

SELECT  *
FROM    USER_dbo_ams_contact C
WHERE   C.orgName IS NOT NULL
        AND C.firstName IS NOT NULL
        AND C.lastName IS NOT NULL;

-- vs. 

SELECT  *
FROM    USER_dbo_ams_contact C
WHERE   C.orgName IS NULL
        AND C.firstName IS NULL
        AND C.lastName IS NULL;

Open in new window


?

You're also aware of the rules of Boolean Distributivity of the Negation:

-- The Negation of

SELECT  *
FROM    USER_dbo_ams_contact C
WHERE   C.orgName IS NULL
        AND C.firstName IS NULL
        AND C.lastName IS NULL;

-- is

SELECT  *
FROM    USER_dbo_ams_contact C
WHERE   NOT ( C.orgName IS NULL
              AND C.firstName IS NULL
              AND C.lastName IS NULL
            );

-- or 

SELECT  *
FROM    USER_dbo_ams_contact C
WHERE   C.orgName IS NOT NULL
        OR C.firstName IS NOT NULL
        OR C.lastName IS NOT NULL;

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Pavel Celba

Just to make it simpler I would write the negation of the expression:

 ([X] Is NULL) AND ([Y] IS NULL) AND ([Z] IS NULL)

this way:
NOT ([X] Is NULL) AND ([Y] IS NULL) AND ([Z] IS NULL)