Using DCount() to Display Counts of Zero

  • 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 o
the five responses. This is done in another query entitled "Q9_qry"
in which I have the following SQL code: Count(RESULTS_BY_DATE_qry.Q9
AS 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 woul
appreciate as much detail as possible.


Thanks
Jo
 
DCount's second argument is simply the name of a table, the name of a query,
or can even be a reference to a form's recordsource
(Forms!FormName.RecordSource) when the form is open.

So if you want to get the results from a query named "RESULTS_BY_DATE_qry"
for field Q9:

DCount("Q9", "RESULTS_BY_DATE_qry")
 
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
 
Thanks, Steve.....I focused on the wrong part of the question....gotta stop
answering posts just before bedtime < yawn! >
 
Back
Top