Monthly and yearly summaries are common in sales, billing, support, and activity reports. In Microsoft Access, the most dependable approach is to group by a real date or numeric year—not by a formatted month name alone.
Last updated: August 28, 2026.
Group totals by month
Suppose Orders contains OrderDate and OrderTotal. The following query returns one row per calendar month:
SELECT DateSerial(Year([OrderDate]), Month([OrderDate]), 1) AS MonthStart,
Count(*) AS OrderCount,
Sum([OrderTotal]) AS MonthlyTotal
FROM Orders
WHERE OrderDate Is Not Null
GROUP BY DateSerial(Year([OrderDate]), Month([OrderDate]), 1)
ORDER BY DateSerial(Year([OrderDate]), Month([OrderDate]), 1);MonthStart is a genuine Date/Time value representing the first day of each month. Format that output column as mmm yyyy or mmmm yyyy in a form or report. Keeping the underlying value as a date ensures that April 2025 sorts before January 2026.
Group by year and month number
If separate numeric columns are more useful for exporting or further calculations, group with Year and Month:
SELECT Year([OrderDate]) AS OrderYear,
Month([OrderDate]) AS OrderMonth,
Count(*) AS OrderCount,
Sum([OrderTotal]) AS MonthlyTotal
FROM Orders
WHERE OrderDate Is Not Null
GROUP BY Year([OrderDate]), Month([OrderDate])
ORDER BY Year([OrderDate]), Month([OrderDate]);Group by year only
SELECT Year([OrderDate]) AS OrderYear,
Count(*) AS OrderCount,
Sum([OrderTotal]) AS AnnualTotal
FROM Orders
WHERE OrderDate Is Not Null
GROUP BY Year([OrderDate])
ORDER BY Year([OrderDate]);Limit the query to a date range
Apply criteria to the original field before grouping. This example summarizes the current year:
SELECT DateSerial(Year([OrderDate]), Month([OrderDate]), 1) AS MonthStart,
Sum([OrderTotal]) AS MonthlyTotal
FROM Orders
WHERE OrderDate >= DateSerial(Year(Date()), 1, 1)
AND OrderDate < DateSerial(Year(Date()) + 1, 1, 1)
GROUP BY DateSerial(Year([OrderDate]), Month([OrderDate]), 1)
ORDER BY DateSerial(Year([OrderDate]), Month([OrderDate]), 1);Filtering the original OrderDate field is preferable to using Year([OrderDate]) = Year(Date()) as the criterion when the table is large, because Access has a better opportunity to use an index on the date field.
Why not group only by Format?
An expression such as Format([OrderDate], "mmmm") produces text. It merges the same month from different years and normally sorts alphabetically. Use a real month-start date for grouping and apply display formatting afterward.
Months with no records
A totals query only returns groups that exist in the source data. If a report must show all twelve months—including months with zero activity—create a small calendar table and left-join the summarized results to it.
