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.
Amour22015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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 FyeOwner, Developing Solutions LLCCommented:
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.
Amour22015Author Commented:
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....
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

LEARN MORE
Dale FyeOwner, Developing Solutions LLCCommented:
no, you misunderstand the "reverse".  The reverse of:

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

is:

([X] IS NOT NULL) OR ([Y] IS NOT NULL) OR ([Z] IS NOT NULL)

That is why I recommended the query I provided in my previous post, to show you that there are 6 possible solutions to the IS NULL/Is NOT NULL combination across three fields.  The (1's) in my previous post should be (-1's).

HTH
Dale

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
<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
Amour22015Author Commented:
Great thanks
ste5anSenior DeveloperCommented:
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

pcelbaCommented:
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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.