Link to home
Start Free TrialLog in
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.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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?
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.
Avatar of Amour22015
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....
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<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
Great thanks
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

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)