I need zero values too

betheball

Registered User.
Local time
Today, 20:24
Joined
Feb 5, 2003
Messages
107
I have a query that returns a count for the number of occurences for all choices in a particular field. However, if the choice's count is zero, it is not returned. How can I get the query to also display the choices tha have zero occurences? Am I making any sense? For example, in field1 is a lookup field with choices of A, B, C or D. I have a query that tells me that there are 3 records with A, 2 with B and 1 with C. D has zero so it doesn't show up in the query, but I need it to. Can I do it? :confused:
 
The lookup table in the following query is named T2
Code:
SELECT
  LkpField
, Count(Field1) AS CntOfOccurance
FROM T2
  LEFT JOIN T1 ON
    T2.LkpField=T1.Field1
GROUP BY LkpField
 
You need to join to the table that contains all the choices.

Select A.YourCode, Count(*) As CodeCount
From tblLookup as A inner join tblOther as B on A.YourCode = B.YourCode
Group By A.YourCode;
 
OK, I am dumber than I thought. I have played with the above suggestions and still can't seem to get what I need. Here are the names of my tables/fields. Maybe if someone writes the answer in kindergartenese I'll understand. Please forgive my denseness.

My main table is called AgeReview. The lookup field is Cause. The lookup table is Age_Cause and the field in the lookup table is Cause. (Could the fact that the field in each table has the same name be part of my problem???)

I am using the following query to give me a count of each occurence of a particular cause in the AgeReview query, but I want a list of all options in the AgeCause in the Age_Cause table and their count, even if the count is zero:

Code:
SELECT AgeReview.Cause, Count(AgeReview.Cause) AS CountOfCause
FROM Age_Causes INNER JOIN AgeReview ON Age_Causes.Cause = AgeReview.Cause
GROUP BY AgeReview.Cause;

Can someone write the exact SQL statement that I would need to get the count of every item in the AgeCause field including those whose count is zero.
 
Last edited:
Here's the query again with adjusted field names
Code:
SELECT
  AC.AgeCause
, Count(AR.Cause) AS CntOfAC
FROM Age_Cause AS AC
  LEFT JOIN AgeReview AS AR ON
    AC.AgeCause = AR.Cause
GROUP BY AC.AgeCause
HTH Nouba
 
Works great. Now maybe you can help me take it a step further. My primary table also has a date field titled ReviewDate. I want to add the date parameter to the above query but still retrieve even results where the value is zero. When I tried adding the date to the above query I only got results for the Causes that had a value > 0. Here's the SQL I used:

Code:
SELECT Age_Causes.Cause, Count(AgeReview.Cause) AS CountOfCause
FROM Age_Causes LEFT JOIN AgeReview ON Age_Causes.Cause = AgeReview.Cause
WHERE (((AgeReview.ReviewDate)=[Enter Review Date]))
GROUP BY Age_Causes.Cause
 
An easy way to do this is to Make a new table (tblNew) with two fields.

So tblNew will have two fields: [Letters] and [LetterCount]
Populate the table With all your record types in [letters] and zeros for the [LetterCount] like this:
A, 0
B, 0
C, 0
D, 0
....


Then in your query link [letters] in tblNew to [letters] your table (tblMain).

Right click on the Join. Choose: all the records from tblNew and only those records from tblMain where the join fields are equal.
Choose the LetterCount from tblMain, and Letters from tblNew.

Run the query:
This will show Null values where there is no data in tblMain.LetterCount... So Now... you can make a new field

LetterCount1: iif([tblMain].[Letters] Is Null, [tblNew].[LetterCount], [tblMain].[LetterCount])

This will make a new field with your counts from the main table and Zeros where you need them from tblNew.

Might be a bit messy but it works.

Later,
Gary
 
Well it takes a while, but I think I got it
Code:
SELECT
  AC.AgeCause
, Count(Q.Cause) AS CountOfCause
FROM Age_Cause AS AC
  LEFT JOIN
    (SELECT
      AR.Cause
    , AR.ReviewDate
    FROM AgeReview AS AR
    WHERE AR.ReviewDate=[Enter Review Date]) AS Q
    ON AC.AgeCause = Q.Cause
GROUP BY AC.AgeCause
 
Nouba, this is probably a dumb question, but what does the "Q" represent?
 
As you can see, there is a select query sitting inside the main query. Normally you would expect there (in the FROM clause) a table or query name. For addressing the fields given back as results of the subquery you need an alias name. I kept it short and used the arbitrary name Q. Though if you like you can rename it, but then you have to change all occurences of Q.

I hope you can follow my explanation.
 

Users who are viewing this thread

Back
Top Bottom