Others

How to Group Access Report Data by 15-Minute Intervals and Hours

Jason MillerJason Miller Nov 3, 2026 870 views

Question details

The user needs to group JobStartTime values in a Microsoft Access report into 15-minute intervals and hours to calculate accurate subtotals and hourly totals.

Product
Microsoft Access
Device & OS
not provided
Scenario
Creating time-based database reports with subtotals categorized by specific quarter-hour intervals.
Observed behavior
Grouping and sorting on separate calculated hour and quarter-hour fields disrupts the chronological order of the time values, often resulting in incorrect report groupings and a False SortOrder status.
Before you start

Ensure you have a backup of your database and open your target query and report in Design View before modifying the sorting and grouping levels.

Solution 1Recommended

Use Calculated Fields for Hour and Quarter-Hour Grouping

Create dedicated calculated fields to extract the exact hour and 15-minute intervals, then structure your report's grouping and sorting parameters to maintain absolute chronological order.

To achieve accurate grouping, you must avoid grouping directly by the full chronological JobStartTime value at the interval levels. Instead, separate the hour and minute intervals into their own calculable fields, group by those, and enforce an underlying chronological sort.

1
Add calculated columns to your query

Open your Access query in Design View. Add two new calculated fields to isolate the hour and the 15-minute block. Type `H: Hour([JobStartTime])` in one column and `Q: Minute([JobStartTime]) \ 15` in the next.

2
Configure Report Grouping

Switch to your Report Design View. Open the 'Group, Sort, and Total' pane at the bottom of the screen. Add a group for the 'H' (Hour) field first, and then add a secondary subgroup for the 'Q' (Quarter-Hour) field.

3
Apply absolute chronological sorting

After configuring the groups, add a sort level using the original 'JobStartTime' field. Set this sort order to 'Ascending' to ensure records within the intervals maintain strict chronological sequencing.

4
Verify SortOrder settings

Check your grouping configurations to ensure no group level disables the sort. The Access documenter should confirm that the SortOrder is properly applied and not returning 'False' for the required levels.

Use Calculated Fields for Hour and Quarter-Hour Grouping
Understanding the Division Operator: The backslash (\) operator used in `Minute([JobStartTime]) \ 15` performs integer division in Access SQL, accurately categorizing minutes 0-14 into 0, 15-29 into 1, and so on.
Free Microsoft Office alternative

Looking for a Free Alternative for Your Daily Office Tasks?

While Microsoft Access is used for complex database reporting, you can manage most of your data analysis, time-tracking, and reporting needs efficiently using WPS Spreadsheet. WPS Office is a free, lightweight suite that is fully compatible with Microsoft Office formats, offering an intuitive interface for seamless data management.

  1. 1. Download and Install WPS Office: Visit the official WPS Office website to download the free suite and install it on your device.
  2. 2. Open WPS Spreadsheet: Launch WPS Spreadsheet and create a new blank document or open an existing file.
  3. 3. Import and Analyze Your Data: Import your database exports as CSV or Excel files, and use powerful Pivot Tables to instantly group your time logs by hours and intervals.
100% free and lightweight office suiteHigh compatibility with Microsoft Excel (.xlsx) formatsFamiliar user interface for easy transition and minimal learning curveBuilt-in data pivot tables and time-filtering features for interval reporting
microsoft office alternative - wps office

Frequently Asked Questions

Why are my 15-minute interval groups out of chronological order in Access?

This usually happens when you group by the original time value directly instead of the calculated hour and quarter-hour fields, or if your report is lacking a final ascending sort on the underlying JobStartTime field.

What does the integer division do in the Access query?

The formula uses integer division (\) to divide the minutes of your time value by 15. It drops the remainder, neatly categorizing any given minute into one of four quarter-hour buckets (0, 1, 2, or 3) for the report groups.

Can I use the Access GroupOn property instead of calculated fields?

While Access allows setting the GroupOn property to 'Hour' or 'Minute', utilizing calculated integer fields for absolute 15-minute intervals generally provides more precise control over subtotals and custom sorting in complex reports.