count if no record, then return zero value

  • Thread starter Thread starter jongellar
  • Start date Start date
J

jongellar

I saw a post on the MSAceessForum from a week or two ago entitled "coun
if no record, then return zero value." I am having the exact sam
problem that the author of this post was having. However, I did no
fully understand the response. Here is the issue, as it relates to m
situation:

I have a query titled "RESULTS_BY_DATE_qry", which contains a fiel
"Q9". For each record, the value in Q9 can be one of the following: 1
2, 3, 4, or 99. I wish to count the number of records which each of th
five responses. This is done in another query entitled "Q9_qry", i
which I have the following SQL code: Count(RESULTS_BY_DATE_qry.Q9) A
CountOfQ9. The resulting query looks something like this:

Q9 CountOfQ9
1 86
2 19
3 2
99 1

Obviously, there are only 4 records because there were no records i
"RESULTS_BY_DATE_qry" that had a value of "4" in the "Q9" field
However, I do not want "Q9_qry" to just skip over that record; rather
I want it to display that there is a count of "0" (zero) for the "4
response.

The suggestion in the previous post was to use the DCount() function
and after investigating what it does, I believe that this would solv
my problem. However, I do not fully understand how this function i
used. In particular, I can't figure out how to set up the "domain
argument (the second argument of the function). Could you pleas
explain in detail how I can set this function up to perform th
operation that I need? I am rather new to Access, so I would appreciat
as much detail as possible.


Thanks

Jo
 
Jon,

No, the DCount function is not applicable in this case.

One easy way around this, which also allows for any eventual change to
the possible responses, is to make a simple, single-field table to store
the allowable Q9 responses. In this table, you willhave 5 records, with
your 1, 2, 3, 4, 99 values entered into the solitary field. Then, add
this table to the query, with a Left Join between the field in the
lookup table and the Q9 field in the RESULTS_BY_DATE_qry query. Instead
of the Q9 field to Group By in your counting query, use the field from
the new lookup table. That way you will get a record returned in your
final query with 0 shown for th count of any values which are not
included in the RESULTS_BY_DATE_qry query. The SQL of the query will be
something along these lines...
SELECT YourLookupTable.PossibleValues, Count(RESULTS_BY_DATE_qry.Q9) AS
CountOfQ9
FROM YourLookupTable LEFT JOIN RESULTS_BY_DATE_qry
ON YourLookupTable.PossibleValues = RESULTS_BY_DATE_qry.Q9
GROUP BY YourLookupTable.PossibleValues
 
Back
Top