A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Grouped/Sorted Report by Month



 
 
Thread Tools Display Modes
  #1  
Old May 24th, 2010, 03:35 AM posted to microsoft.public.access.reports
SCS
external usenet poster
 
Posts: 3
Default 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.

Ads
  #2  
Old May 24th, 2010, 04:09 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:08 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.