If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grouped/Sorted Report by Month
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. |
#2
|
|||
|
|||
Grouped/Sorted Report by Month
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] |
Thread Tools | |
Display Modes | |
|
|