Avatar of Ludique
LudiqueFlag for United Kingdom of Great Britain and Northern Ireland asked on

Count records including rows that add up to Zero (Nz function?)

The query below adds up how may policies have been submitted per business origin.  e.g. Referral, Repeat Customer, Advert.

I would like to include all Policy Origins even if the result is Zero.

I've been searching solutions in ee and kinda think that I need Nz somewhere in the SQL but after several experiments can't figure out where to put it.
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPolicyOrigin.Origin,(Count(qryPolicyNoDate.PolicyID) AS Total
FROM qryPolicyNoDate RIGHT JOIN tblPolicyOrigin ON qryPolicyNoDate.OriginID = tblPolicyOrigin.OriginID
WHERE (((qryPolicyNoDate.DateSubmitted) Between [Enter Start Date] And [Enter End Date]))
GROUP BY tblPolicyOrigin.Origin;

Open in new window

SQL

Avatar of undefined
Last Comment
Ludique

8/22/2022 - Mon
Terry Woods

I would expect your query to work already actually, except for there being an extra bracket on the line starting with SELECT. Are you sure it's excluding Policy Origins if the result is zero?

Corrected here:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPolicyOrigin.Origin,Count(qryPolicyNoDate.PolicyID) AS Total
FROM qryPolicyNoDate RIGHT JOIN tblPolicyOrigin ON qryPolicyNoDate.OriginID = tblPolicyOrigin.OriginID
WHERE (((qryPolicyNoDate.DateSubmitted) Between [Enter Start Date] And [Enter End Date]))
GROUP BY tblPolicyOrigin.Origin;

Open in new window

ASKER
Ludique

Bracket duly removed (it wasn't doing any harm - just left there when I was optimistically trying to get Nz in there somewhere)

I'm quite sure that it's excluding policy origins - there are 11 in the list but it only lists 3 - the ones which have a total of more than 0
nexusnation

I also don't see anything wrong with your query.  I'm trying to think... I must be missing something. Can we have some sample data?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Thomasian

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Ludique

Oh dear.  That works.  Unfortunately, there are loads of old policies which have no start date and I don't want to include them.

Is it possible the use Nz on the (Count(qryPolicyNoDate.PolicyID) bit of the SELECT?
Thomasian

Using NZ on COUNT will do nothing since COUNT will never return null. How can you tell if a policy has no start date? Does it have a start date field?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Thomasian

If start date is in table PolicyOrigin, then the query will be something like this.
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPolicyOrigin.Origin,(Count(qryPolicyNoDate.PolicyID) AS Total
FROM qryPolicyNoDate RIGHT JOIN tblPolicyOrigin ON qryPolicyNoDate.OriginID = tblPolicyOrigin.OriginID
WHERE (qryPolicyNoDate.DateSubmitted IS NULL OR (((qryPolicyNoDate.DateSubmitted) Between [Enter Start Date] And [Enter End Date])))
      AND tblPolicyOrigin.StartDate IS NOT NULL
GROUP BY tblPolicyOrigin.Origin;

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Ludique

tblPolicy has a field called StartDate.
About 1/10 of the records are very old and imported from ancient spreadsheets so don't have a StartDate

My original code returns this:

Origin                               Total
Referrals                      2
Repeat customer              19
Sales visits                    13

A slightly amended New Version of the above returns this:
Origin                              Total
_Not Selected                 50
Marketing lists                  50
Referrals                    50
Referrals Pro                 50
Telephone sales               50
Cold calling                  50
Sales visits                 50
Postal sales                50
Direct offer                 50
Repeat customer            50
Internet sales               50
Other                           50

I'm hoping for:
_Not Selected                0
Marketing lists                 0
Referrals                    2
Referrals Pro                 0
Telephone sales              0
Cold calling                  0
Sales visits                  12
Postal sales                0
Direct offer                0
Repeat customer           13
Internet sales               0
Other                           0
'Original Code
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPolicyOrigin.Origin, Count(qryPolicyNoDate.Origin) AS Total
FROM qryPolicyNoDate RIGHT JOIN tblPolicyOrigin ON qryPolicyNoDate.OriginID = tblPolicyOrigin.OriginID
WHERE (((qryPolicyNoDate.DateSubmitted) Between [Enter Start Date] And [Enter End Date]) AND ((qryPolicyNoDate.AgencyID)=1))
 GROUP BY tblPolicyOrigin.Origin;
 
'New Version
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPolicyOrigin.Origin,(select Count(qryPolicyNoDate.PolicyID)
                                 from qryPolicyNoDate
                                 where qryPolicyNoDate.Origin = tblPolicyOrigin.Origin
                                   and qryPolicyNoDate.DateSubmitted Between [Enter Start Date] And [Enter End Date]
                                   and qryPolicyNoDate.DateSubmitted is not null
                              ) AS Total
FROM tblPolicyOrigin

Open in new window

Thomasian

Have you tried my last post? http:#a21666154

>>tblPolicy has a field called StartDate.
Do you mean tblPolicyOrigin? If not, then how is this table linked/joined to the other tables?

Slight correction, it is better to use OriginID to check for null.
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPolicyOrigin.Origin,(Count(qryPolicyNoDate.PolicyID) AS Total
FROM qryPolicyNoDate RIGHT JOIN tblPolicyOrigin ON qryPolicyNoDate.OriginID = tblPolicyOrigin.OriginID
WHERE (qryPolicyNoDate.OriginID IS NULL OR (((qryPolicyNoDate.DateSubmitted) Between [Enter Start Date] And [Enter End Date])))
      AND tblPolicyOrigin.StartDate IS NOT NULL
GROUP BY tblPolicyOrigin.Origin;

Open in new window

Terry Woods

Ludique, your query looks ok to me, except I generally prefer to use "count(*)" instead of "count(qryPolicyNoDate.PolicyID)". You might like to see if that fixes it.
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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Ludique

I entirely agree re points.  This is my first foray into using the SQL view in MSAccess - I've usually been able to just use Design Query in Access.  When I'm back at work on Monday evening I shall try all of the suggestions (even if the very first one works anyway) partly to report back, but also because the detail of your different replies gives me an excellent opportunity to learn more about the flexibility that (I've been told) SQL offers - thank you all
ASKER
Ludique

Sorted it out and learnt a lot in the process.  It got even more complicated when I realised it was going to be used in a crosstab query with Origin as rows and Advisers as columns.  I've attached an example of one of the successful attempts.

However, on reflection, I decided to change the design of the graphs using the data in Excel so we don't need the Zero's anymore and I don't have to apply the solution to 80 odd different queries.

Thank you all so much anyway.  The learning curve has been steep but swift!
TRANSFORM Sum(sub.Total) AS SumOfTotal
SELECT sub.Origin
FROM [
  SELECT qryPolicyNoDate.AdviserInitials, qryPolicyNoDate.Origin, COUNT(*) As Total
    FROM qryPolicyNoDate
    GROUP BY qryPolicyNoDate.AdviserInitials, qryPolicyNoDate.Origin
  UNION
  SELECT LookUpStaffAdviser.AdviserInitials, '', 0
    FROM LookUpStaffAdviser
]. AS sub
GROUP BY qryPolicyNoDate.Origin
PIVOT qryPolicyNoDate.AdviserInitials;

Open in new window