Prev: new crosstab option?
Next: Hide Sub Report(s) when records are null
From: SCS on 23 May 2010 22:35
I have a fairly simple access database. I've created a report grouped by
Category and Month... with a total per month. My problem is that the
months with no total don't show on the report. Is there a way to make the
month show with a zero total?

ie.
January - 24 orders
February - 30 orders
March - 0 orders
April - 28 orders
********This is what I'd like to see ... each month listed. Currently,
March doesn't show at all on my report.

Any help would be appreciated.

From: Marshall Barton on 23 May 2010 23:09
SCS wrote:
>I have a fairly simple access database. I've created a report grouped by
>Category and Month... with a total per month. My problem is that the
>months with no total don't show on the report. Is there a way to make the
>month show with a zero total?
>
>ie.
>January - 24 orders
>February - 30 orders
>March - 0 orders
>April - 28 orders
>********This is what I'd like to see ... each month listed. Currently,
>March doesn't show at all on my report.
>


To make up records that don't really exist, create a table
(named tblMonths) with one field (named MonthNum). Populate
the table with the values 1 through 12. Then join that
table to your existing table using an outer join:

SELECT table.Category, table.Month, table/datefield, ...
FROM tblMonths LEFT JOIN table
ON tblMonths.MonthNum = Month(datefield)

You should then get a record (all fields contain Null) for
months that are not in your table.

Note: That kind of ON claause can not be represented in the
query designer, so you will have to use SQL view.

You problem is probably not that simple, especially if you
need to take the year into account, but we will need more
information to deal with whatever else you need to get the
report working. For example, how can you tell if a category
does not exist or if it just did not have any orders in a
month.

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: new crosstab option?
Next: Hide Sub Report(s) when records are null