Others

How to Create a Decade-Based Book Publication Graph in Access

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to create a query in Microsoft Access that groups book records by their publication decade to serve as an automatically updating data source for a bar chart.

Product: Microsoft Access
Device & OS: not provided
Scenario: Generating dynamic charts or reports based on categorized date ranges.
Observed behavior: Records need to be aggregated by standard decade formats (e.g., 2020s) and must dynamically feed calculated counts into an Access chart visualization.

Before you start

Ensure your Microsoft Access database contains a table with book records and that the publication date field is properly formatted as a Date/Time data type.

Solution 1Recommended

Use an Integer Division Expression to Group Dates by Decade

Apply an integer division formula in your Access query to dynamically calculate and group publication dates into standard decades.

This method utilizes integer division (using the backslash operator) to floor the publication year to the nearest ten. This effectively creates standard decade intervals like 1980–1989 and outputs them in an easy-to-read format.

Create a new query

Open your Microsoft Access database, navigate to the Create tab on the ribbon, and click on Query Design. Add the table containing your book publication records to the design grid.

Add the decade expression

In an empty column within the query design grid, type the expression `Decade: (Year([PublicationDate])\10)*10 & "s"`. Replace `[PublicationDate]` with the actual name of your date field.

Enable Totals and group the records

Click the Totals button (the Sigma symbol) in the ribbon to add a Total row to your query grid. Ensure the Total row for your new Decade column is set to Group By.

Count the books per decade

Add a unique identifier field (such as BookID) to the next column. Change its Total row setting to Count so that Access calculates the total number of books published within each grouped decade.

Use the query for your chart

Save the query. You can now select this query as the Record Source when creating a new bar chart or report, ensuring your graph updates automatically as new books are added to the database.

Free Microsoft Office alternative

Discover WPS Office for Data Visualization

While Microsoft Access manages complex relational databases, WPS Office provides a free, lightweight alternative for data analysis and charting. You can easily group records and create dynamic decade-based charts using WPS Spreadsheet without needing database expertise.

1. Import data into WPS Spreadsheet: Open WPS Spreadsheet and import or paste your database records containing the book publication dates.
2. Calculate the decade: Create a new column next to your dates and use the formula `=FLOOR(YEAR(A2), 10) & "s"` to automatically assign each record to a decade.
3. Create a PivotChart: Select your entire data range, go to the Insert tab, and choose PivotTable. Once generated, insert a PivotChart to easily visualize your book publications categorized by decade.

Free and lightweight suite including Writer, Spreadsheet, Presentation, and PDF tools.High compatibility with Microsoft Office formats, including full support for Excel (.xlsx) files.Easily handle large datasets and create dynamic charts using built-in spreadsheet functions and PivotTables.Familiar, easy-to-use interface with a seamless migration path and no steep learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the expression use integer division instead of standard division?

In Microsoft Access, integer division (using the backslash `\`) automatically drops any decimal remainder, whereas standard division (using the forward slash `/`) retains decimals. Using `\10` perfectly floors the publication year to the nearest whole ten, creating clean decade groupings.

How can I change the grouped decade format to display as a range like 1980-1989?

You can modify the query expression to string together the start and end years dynamically. Instead of adding an "s", use an expression like `((Year([PublicationDate])\10)*10) & "-" & (((Year([PublicationDate])\10)*10)+9)` in your query design grid.

Will the Access bar chart sort the decades chronologically?

Yes. Because the resulting decade strings (e.g., "1990s", "2000s") are alphabetically sequential in a way that matches numerical chronological order, applying a standard ascending sort on the Decade column in your query will accurately arrange the bars from oldest to newest.

Office Quick Solutions