Others

Calculate Previous Month's Total and Percentage Change in Access Query

Natalie TaylorNatalie Taylor Oct 14, 2026 868 views

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.

How to Reference the Previous Month's Total in an Access Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the Monthly Totals Query

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'.

2
Retrieve the Previous Month's Total

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'.

3
Calculate the Percentage Change

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.

4
Format the Query Output

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'.

Use a Grouped Query and Self-Join for Month-Over-Month Variance
Date Standardization Logic: The expression [OpenDate]-Day([OpenDate])+1 effectively forces every date in a given month to the 1st of that month (e.g., April 15 becomes April 1). This ensures proper grouping regardless of which specific day a transaction occurred.
Free Microsoft Office alternative

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. 1. Import Data: Open WPS Spreadsheet and paste or import your raw transaction data containing the dates.
  2. 2. Insert PivotTable: Navigate to the Insert tab and click PivotTable, selecting your entire transaction data range.
  3. 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. 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.
Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in PivotTables to quickly analyze month-over-month percentage changes without complex SQL.Free, lightweight, and fast-loading office suite.Familiar tabbed interface ensures seamless migration with zero learning curve.
QA img-9

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.