query groupby NOT counting Nulls

I'm using the following query:

SELECT Count(dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status) AS CountOfSchedule_Phase_Status
FROM dbo_t_dispensing_metrics_detail_schedules
GROUP BY dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status
HAVING (((dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status) Not Like "*sign*")) OR (((dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status) Is Null))
ORDER BY dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status DESC;

Open in new window


But it is not counting my fields with Null Values ??


Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
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.

Saurabh Singh TeotiaCommented:
Use this query...

select count(*) AS CountOfSchedule_Phase_Status
from dbo_t_dispensing_metrics_detail_schedules tb1
where (tb1.Schedule_Phase_Status Not Like "*sign*");

Open in new window


Saurabh...
0
Dale FyeCommented:
That is correct, by design.  Try:

SELECT NZ(Schedule_Phase_Status, "") as Status
, Count(*) AS CountOfStatus
FROM dbo_t_dispensing_metrics_detail_schedules
GROUP BY NZ(Schedule_Phase_Status, "")
HAVING NZ(Schedule_Phase_Status, "") Not Like "*sign*"
ORDER BY NZ(Schedule_Phase_Status, "") DESC;
0
HuaMin ChenProblem resolverCommented:
Try
SELECT Count(dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status) AS CountOfSchedule_Phase_Status
FROM dbo_t_dispensing_metrics_detail_schedules
where isnull(dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status,'')<>''
GROUP BY dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status
HAVING (((dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status) Not Like "*sign*")) OR (((dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status) Is Null))
ORDER BY dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status DESC;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
You could also try:

SELECT NZ(Schedule_Phase_Status, "") as Status
, Count(*) AS CountOfStatus
FROM dbo_t_dispensing_metrics_detail_schedules
WHERENZ(Schedule_Phase_Status, "") Not Like "*sign*"
GROUP BY NZ(Schedule_Phase_Status, "")
ORDER BY NZ(Schedule_Phase_Status, "") DESC;

The difference between using a WHERE clause and a HAVING clause is that the WHERE clause causes JET to filter out all of the records which do not meet your criteria before the grouping and aggregation operation.  The HAVING clause causes this to occur after the grouping and aggregation, so it can be slower.

If you are building your query in the query grid, the way to get the WHERE clause is to add a second version of the field you want to set the criteria on, and uncheck the checkbox indicating whether to include the column in the output.
0
FordraidersAuthor Commented:
Saurabh...i have tried this before posting..did not work...thanks.

dale, this is what i keep getting...see pic attached. per both query suggestions
double line on result query

huaminchen.
getting error on syntax.
where isnull(dbo_t_dispensing_metrics_detail_schedules.Schedule_Phase_Status,'')<>
0
FordraidersAuthor Commented:
fyi...and sorry my mistake...this is a linked table to sql server...the field where trying to capture nulls  is  varchar "Schedule_Phase_Status"
0
FordraidersAuthor Commented:
dale, revised the sql a bit and this works:

SELECT Count(*) AS CountOfStatus
FROM dbo_t_dispensing_metrics_detail_schedules
WHERE (((Nz([Schedule_Phase_Status],"")) Not Like "*sign*"));
0
Dale FyeCommented:
Glad you figured it out.

So, all you wanted was a single record count of all records where the Schedule_Phase_Status column was not like "*sign*"?

You obviously understand that the reason you got to lines with one containing a ZLS in the Status column was because of the GroupBy.  In order to get Access (or any other RDBMS for that matter) to Count records where the value is NULL, you need to:
1.  Count a field that will have values, or force the field to have a value with the NZ() function.  You failed to take this into account in your original query
2.  Make sure that the WHERE clause is not filtering on a field that can contain NULLs.  If it does, then you need to include nulls in the critiera.  You did that in your original query.
0

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
FordraidersAuthor Commented:
Thanks very much
0
Dale FyeCommented:
You're welcome.
0
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.