Zero in "Count" in group-by query

[Zippy the Pinhead]

I have a database in which I keep track of cases. I have to run
queries that count for the occurrence of certain events and sum these
occurrences by quarter or by month depending on the prevalence.

I would like to query's output grid to show a zero (0) in the periods
during which there are no occurrences. Instead, the query's output
grid will contain no entry for which there are no occurrences. That's
inconvenient when, for example, I copy the contents of the query
output grid to Microsoft Excel or Minitab for graphing, control
charts, or analysis.

How can I make Access put a zero in the output query instead of
skipping the months or quarters during which the event in question
didn't happen?

Thanks.

 

[Allen Browne]

Switch the query to SQL View (View menu), and use Nz() to convert the nulls
to zero.

For example, where you see:
Sum([Amount])
change it to:
Nz(Sum([Amount], 0)

JET is likely to lose track of the data type when you do this, so you
probably want to typecast as well, e.g.:
CCur(Nz(Sum([Amount], 0))
or:
CLng(Nz(Sum([Amount], 0))

 

[Allen Browne]

Re-reading your question, my reply probably did not address the issue. Nz()
will convert a null to zero, but it will not add missing rows to the query.

If there were months/quarters where there were no entries, then the GROUP BY
query will not have an entry for the month. Were you asking how to get those
months there, even if there were no transactions in the period?

If so, the months have to come from somewhere. You will need to create a
table, with a record for the first of each month. Your GROUP BY query will
need to group by:
DateSerial(Year([TransDate], Month([TransDate]), 1)
where TransDate represents the name of the transaction date field. You will
then be able to create a 2nd query that has two input "tables":
- the table of months, and
- the group by query.
Join the 2 "tables" on the date field.
Double-click the line joining the 2 tables, and choose the option:
All records from MonthTable, and any matches from GroupByQuery.
Add criteria to limit the dates in the month table to the period of interest
(e.g. a financial year).

The resultant query will have a record for each date in the period (source
from the month table), and will show the grouped data for all matches where
there are any.

Hope that's not too confusing.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Switch the query to SQL View (View menu), and use Nz() to convert the
nulls to zero.

For example, where you see:
Sum([Amount])
change it to:
Nz(Sum([Amount], 0)

JET is likely to lose track of the data type when you do this, so you
probably want to typecast as well, e.g.:
CCur(Nz(Sum([Amount], 0))
or:
CLng(Nz(Sum([Amount], 0))

I have a database in which I keep track of cases. I have to run
queries that count for the occurrence of certain events and sum these
occurrences by quarter or by month depending on the prevalence.

I would like to query's output grid to show a zero (0) in the periods
during which there are no occurrences. Instead, the query's output
grid will contain no entry for which there are no occurrences. That's
inconvenient when, for example, I copy the contents of the query
output grid to Microsoft Excel or Minitab for graphing, control
charts, or analysis.

How can I make Access put a zero in the output query instead of
skipping the months or quarters during which the event in question
didn't happen?

 

[Zippy the Pinhead]

Re-reading your question, my reply probably did not address the issue. Nz()
will convert a null to zero, but it will not add missing rows to the query.

If there were months/quarters where there were no entries, then the GROUP BY
query will not have an entry for the month. Were you asking how to get those
months there, even if there were no transactions in the period?

There would be entries for nearly every day, but occurrences of the
event of interest might not happen for a given month or even a given
quarter. So other fields in each record might have entries, but if
there's no output from the "count", the month or quarter will not be
included in the query output.

I'll try both solutions you posted, thanks.

 

[Doug Munich]

If you are counting "events" for each "case" and these are kept in
separate related tables, you can have your query return all cases whether or
not they have events by changing the relationship between the tables from
"only include rows where joined fields are equal", to "include all records
from <<cases>> and only those recs from <<events>> where joined fields
equal".

Doug