Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005
    Posts
    1,191

    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:

    Code:
    'Create Table
    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')), " & _
                    "(Year(tblData.[CTSDate])*12+Month(tblData.[CTSDate])-1);"
    If ObjectExists("Table", strTable) Then DoCmd.DeleteObject acTable, strTable
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    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?
    Last edited by nckdryr; 01-20-09 at 02:22.
    Me.Geek = True

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    Code:
    SELECT Format(DateAdd("m",i,'2009-01-01'),"YYYY MM") AS YYYY_MM
      FROM integers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •