Others

How to Create a Dynamic Rolling Sales Query in Access

WPS EditorWPS Editor Oct 12, 2026 869 views

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.

How to Create a Dynamic Rolling Sales Query in Access
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a User Input Form

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.

2
Declare the Form Parameter

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.

3
Calculate the Base Date

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.

4
Apply Conditional SUM for Rolling Periods

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

5
Group by Item Number

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

Build a Rolling Sales Query using Date Functions and Conditional Sums
Query Setup Complete: Your query will now dynamically adjust and recalculate the 1-to-12-month sales periods whenever the user changes the date on the input form.
Free Microsoft Office alternative

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. 1. Export Query Results: In Access, right-click your completed dynamic rolling sales query and export it as an Excel (.xlsx) file.
  2. 2. Open with WPS Spreadsheet: Launch WPS Office, open the exported spreadsheet, and verify your rolling total columns.
  3. 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.
Fully compatible with Microsoft Excel formats (.xlsx, .csv) for seamless database exports.Advanced built-in pivot tables and charting tools to visualize multi-month sales trends.Free, lightweight, and requires minimal system resources compared to heavy database tools.Familiar tabbed interface combining spreadsheets, documents, presentations, and PDFs in one window.
microsoft office alternative - wps office

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.