Why does Access make me format my dates when grouping by month with an aggregate

[rbennion]

Title pretty much says it all. I am confused why i can't simply use the following code for the year 2016:



SELECT Month(Discharge_Date), Sum(#_OCR_Sessions)
FROM [General]
WHERE YEAR(Discharge_Date) = '2016'
GROUP BY Month(Discharge_Date);

I got the query to run, (honestly in a better way) by using the following:

SELECT Format([Discharge_Date],"yyyy"", ""mm") AS [Month], Sum([#_OCR_Sessions]) AS [SumOfSessions]
FROM [General]
GROUP BY Format([Discharge_Date],"yyyy"", ""mm");


This shows all years and the months of those years, which is nice.

My question is purely out of curiosity, why does it make me format when i'm using an aggregate?

Thanks!

[June7]

Month is a reserved word. Should not use reserved words as names. Also advise no spaces or punctuation/special characters (underscore only exception).


If you prefer:

SELECT Year([Discharge_Date]) AS Yr, Month([Discharge_Date]) AS Mo, Sum([#_OCR_Sessions]) AS [SumOfSessions]
FROM [General]
GROUP BY Year([Discharge_Date]), Month([Discharge_Date]);