How to Create a Dynamic Rolling Sales Query in Access
Question details
The user needs to create an Access query that dynamically calculates one-, three-, six-, nine-, and twelve-month rolling sales totals based on a user-selected ending date.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Generating flexible, date-driven sales queries where aggregated sales totals over multiple month intervals adapt automatically to an input end date.
- Observed behavior
- The solution requires using a form control as a DATETIME parameter, employing DateSerial to find the base month's start, and utilizing DateAdd within conditional SUM statements grouped by ItemNumber.
Ensure your Access database contains a valid sales table featuring at least an item number identifier, a transaction date field, and a sales amount field.
Build a Rolling Sales Query using Date Functions and Conditional Sums
Use a form control to accept the ending date, then apply Access date functions and IIF logic to dynamically aggregate rolling totals.
To achieve dynamic date intervals without hardcoding criteria, the query must reference a user input form and mathematically determine the date boundaries using DateSerial and DateAdd.
Build an Access form and insert an unbound text box control (e.g., named 'txtEndDate') where the user can input their desired ending date for the sales report.
Open your query in Design View. Go to the 'Query Setup' group on the ribbon, click 'Parameters', and declare your form control (e.g., [Forms]![YourFormName]![txtEndDate]) explicitly as a DATETIME Data Type.
Use the DateSerial function in a calculated field to determine the first day of the selected month. This creates a reliable anchor point for your rolling periods regardless of how many days are in the ending month.
Create calculated fields for each period (1, 3, 6, 9, 12 months) using a combination of SUM, IIF, and DateAdd. For example, for a 3-month total, the logic evaluates if the sale date is greater than or equal to DateAdd('m', -3, BaseDate).
Click the 'Totals' button (Sigma icon) in the ribbon to turn on aggregation. Set the 'ItemNumber' field to 'Group By' and ensure your conditional period fields are set to 'Expression'.

Analyze and Share Your Sales Data with WPS Office
While Microsoft Access handles complex database queries, analyzing and distributing those rolling sales reports is easier in a spreadsheet. Export your dynamic Access query results and seamlessly process them using WPS Spreadsheet. As a free, lightweight alternative to Microsoft Office, WPS Office gives you all the tools you need for robust data analysis.
- 1. Export Query Results: In Access, right-click your completed dynamic rolling sales query and export it as an Excel (.xlsx) file.
- 2. Open with WPS Spreadsheet: Launch WPS Office, open the exported spreadsheet, and verify your rolling total columns.
- 3. Visualize the Trends: Highlight your data and insert a Line or Bar chart to visualize how your sales are rolling over the 1, 3, 6, 9, and 12-month intervals.

Frequently Asked Questions
Can I export this dynamic Access query to Excel?
Yes. Once the query runs and produces the rolling totals, you can right-click the query tab or use the External Data menu to export the results directly to an Excel file. This is useful if the data is part of a larger spreadsheet-based exercise.
Why must the form control be declared as a DATETIME parameter?
Declaring the parameter explicitly as DATETIME tells the Access database engine to treat the user's input strictly as a date. If left undeclared, Access might interpret the input as text, which causes type mismatch errors when executing date-math functions like DateAdd.
How does DateSerial help calculate the first day of the month?
DateSerial(Year(Date), Month(Date), 1) constructs a valid date by extracting the year and month from the user's input while forcing the day value to '1'. This ensures the DateAdd function subtracts months cleanly from the start of the period.
How do conditional SUM expressions work in Access queries?
Conditional summing in Access is achieved by wrapping an IIF function inside a SUM function. The IIF checks a condition (e.g., is the transaction date within the 3-month window). If true, it returns the sales amount to be summed; if false, it returns zero.




