var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Missing Months in GROUP BY Query
I have a chart on a report that references a table I create right before I open the report with the following query:
This works well enough. The problem is that if, say, it only finds records for June, July and September, than the chart only shows values for those three months, and August is nowhere to be found: Is it possible to show a "0" for August's SumValue and CntValue in this case?
SysCmd acSysCmdSetStatus, "Tabulating Data..."
strTable = "tblCTSMonthChartData"
strSQL = "SELECT Format(tblData.[CTSDate],'YYYY MMM') AS [CTSMonth], " & _
"Sum(tblData.[Amt]) AS [SumValue], " & _
"Count(tblData.[Amt]) AS [CntValue] " & _
"INTO [" & strTable & "] " & _
"FROM tblData " & _
"WHERE " & Me.txtboxSQLWhere & " " & _
"GROUP BY (Format(tblData.[CTSDate],'YYYY MMM')), " & _
If ObjectExists("Table", strTable) Then DoCmd.DeleteObject acTable, strTable
Last edited by nckdryr; 01-20-09 at
Me.Geek = True
yes, it's possible, assuming you have (or can generate within the query) a table of all the months you want, from which you can LEFT OUTER JOIN to your data
generating within the query is simple if you have a table of integers
for example, assume you have a table called integers with one column, let's call it i, and there are 12 rows, with i=0 through i=11
SELECT Format(DateAdd("m",i,'2009-01-01'),"YYYY MM") AS YYYY_MM