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
Therefore we design a report like this
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 – 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.
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”
Click on preview, now you get the correct grouping by date.
