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));
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
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
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 CWHERE C.orgName IS NOT NULL AND C.firstName IS NOT NULL AND C.lastName IS NOT NULL;-- vs. SELECT *FROM USER_dbo_ams_contact CWHERE C.orgName IS NULL AND C.firstName IS NULL AND C.lastName IS NULL;
-- The Negation ofSELECT *FROM USER_dbo_ams_contact CWHERE C.orgName IS NULL AND C.firstName IS NULL AND C.lastName IS NULL;-- isSELECT *FROM USER_dbo_ams_contact CWHERE NOT ( C.orgName IS NULL AND C.firstName IS NULL AND C.lastName IS NULL );-- or SELECT *FROM USER_dbo_ams_contact CWHERE C.orgName IS NOT NULL OR C.firstName IS NOT NULL OR C.lastName IS NOT NULL;
- 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?