Access Report group by date problem

This Access tutorial explains how to group by date (one date for each group) in Access Report and explain the issue.

You may also want to read:

Access Report keep a group together in one page

Access Report group by date problem

When you create an Access Report, the default setting isn’t group by date.

To illustrate, assume that we have an employee database as below.

Hire Date Employee ID
20/12/2016 001
20/12/2016 002
21/12/2016 003
21/12/2016 004
22/12/2016 005
1/1/2017 006


Our  goal is to create a report to group by date as below

Access Report group by date 02


Therefore we design a report like this

Access Report group by date 01


Click to preview, now instead of getting day by day result, somehow some dates are grouped together, this is different from our goal.

Access Report group by date 02

Access Report group by date – Solution

To find out the reason why, go back to Report Design View, at the bottom for the Grouping that you previously set for Hire Date, click “More” to see more available options.

Access Report group by date 03

You can see that by default, date is grouped by quarter, not by date. This explains why December dates are in one group while January date is in another group. (Q1 is Jan to Mar, Q2 is Apr to Jun, Q3 is Jul to Sep, Q4 is Oct to Dec)

To change the grouping back to date, select “by entire value” or “by day”

Access Report group by date 04


Click on preview, now you get the correct grouping by date.

Access Report group by date 05


Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *