Calculate Previous Month's Total and Percentage Change in Access Query
Question details
The user needs to create a Microsoft Access query to calculate monthly transaction totals, look up the total from the previous month, and compute the month-over-month percentage change.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Generating a reporting query to track monthly transaction trends and calculate growth percentages.
- Observed behavior
- Requires a SQL method to group dates by month, perform a lookup for the previous month's grouped total, and calculate the mathematical variance.
Ensure your transaction table contains a valid Date/Time field and that you are familiar with switching to the SQL View within the Access Query Designer.
Use a Grouped Query and Self-Join for Month-Over-Month Variance
Standardize the transaction dates to the first of each month, group the totals, and calculate the variance against the previous month using a calculation expression.
To compare data across consecutive months in Access, you first need to align all dates to a common denominator (the 1st of the month) so they can be grouped accurately. After generating these monthly totals, a second query or a self-join is used to match the current month with the previous month.
Go to the Create tab, click Query Design, and switch to SQL View. Enter the following SQL: SELECT [OpenDate]-Day([OpenDate])+1 AS TheMonth, Count(Transactions.Type) AS Total FROM Transactions GROUP BY [OpenDate]-Day([OpenDate])+1. Save this query as 'MonthlyTotals'.
Create a new query that references your 'MonthlyTotals' query. Use a self-join or a DLookup function to retrieve the 'Total' where 'TheMonth' is exactly one month prior to the current record's 'TheMonth'.
In this new query, add a calculated field using the expression: [Total]/[PreviousTotal]-1. This formula computes the growth or decline relative to the prior month.
Open the Property Sheet for your fields. Set the Format property for 'TheMonth' to 'mm-yyyy' and set the Format property for the calculated variance field to 'Percent'.

Analyze Monthly Data Easily with WPS Spreadsheet
While Microsoft Access is powerful for relational databases, month-over-month calculations are often faster and more visual in a spreadsheet. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, featuring robust PivotTables to track data trends effortlessly.
- 1. Import Data: Open WPS Spreadsheet and paste or import your raw transaction data containing the dates.
- 2. Insert PivotTable: Navigate to the Insert tab and click PivotTable, selecting your entire transaction data range.
- 3. Group by Month: Drag your Date field to the Rows area, right-click to Group by Months, and drag your Transaction ID to the Values area to Count.
- 4. Calculate Percentage Change: Add the Transaction field to the Values area a second time, right-click it, select 'Show Values As', and choose '% Difference From' the previous month.

Frequently Asked Questions
Why do I get a data type mismatch error when looking up previous totals?
This usually happens if the date fields you are comparing are not strictly set as Date/Time data types or are formatted differently. If your dates are stored as text strings, Access cannot perform the mathematical date operations required to subtract a month.
Can I build this monthly total calculation in Query Design View instead of SQL?
Yes. You can use the Expression Builder in Query Design View to write TheMonth: [OpenDate]-Day([OpenDate])+1. Then, click the Totals button (the Sigma icon) on the ribbon to activate grouping, and select 'Group By' for this new column.
How do I handle months with zero transactions in my query?
Standard grouping queries only display months where data exists. If a month has no transactions, it will be skipped, which disrupts the previous month lookup. To fix this, create a separate continuous calendar table of all months and use a LEFT JOIN to link it to your transaction data.




