Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


MS Access Report with Grouping

MS Access Report Using Groups and Custom Paging

This article describes how to create an Access Report and Group the report by the performance of the sales people in the AdventureWorks database. The article will also show how to implement custom paging in the report so that each Group has its own number of pages. For example, the 1st Group would display Page 1 of 2, Page 2 of 2 and the next group would then start over with Page 1 of 2, Page 2 of 2.

Setting the Data Source

The report will be using a table that I created from a View in the AdventureWorks database in SQL Server. It contains the Sales Person data and includes their YTD Sales totals. The View added a column which provided a Performance Rating based on the amount in the SalesYTD column. It did not contain a very large number of records, so I added more salespeople to it with amounts and PerformanceRatings.

Created View for MS Access

Setting Up Grouping and Sorting in MS Access

Set up the RecordSource for the report to the table tblSalesPerformance by right clicking on the upper left hand square and selecting properties from the Context Menu. You can select it from the combo box next to the RecordSource field in the Property Sheet.

MS Access Report

Next, we go to the bottom of the report to the Group, Sort, and Total section. If it is not appearing at the bottom of the report, look for the Group & Sort icon in the Ribbon. You can toggle in and out of the Grouping Section by clicking on it.

Grouping Sorting section of MS Access Report

By clicking on the Add a Group area, you can select which field to Group on, in this report it is the PerformanceRating field. I also added a Sort By column of ID. In each Group, I would like to see the salespeople sorted by their ID, although I could have used their name of their Sales Year To Date (SalesYTD) numbers as well.

Set Group By and Sort By functions in MS Access Report

Add some fields in the Detail section of the report and format them and then add some labels and a line in the Group Header portion of the report and format them so they will look nice for your boss or client who will be impressed with your well laid out report.

MS Access Report with Group Header

Finally, run the report to see how it looks in Print Preview. Notice that it looks very nice, but it needs Page Numbers.

MS Access Group Header Report Preview

Setting Up Custom Paging in MS Access

If I add the Page N of M field from the Page Numbers button on the Ribbon and Pop Up, I will have paging on the report, but notice that it is for the entire report. I would like to have the Page Numbers for each Group so that the first group will have Page 1 of 1, the second group will have Page 1 of 1, and my third group will have Page 1 of 2 and Page 2 of 2.

Page Numbers in MS Access Report

I found a solution to this on the Microsoft website called How to reset the Page Number and Page Count in MS Access. The article was written for MS Access 2003, so in case it ever disappears from the Microsoft Knowledgebase, here are the steps.

Create a table called Category Page Numbers in the MS Access database with your Field Name for the Group and another field of datatype Number with a Field Name of Page Number.

Page Number MS Access Table

Create a TextBox in the Footer called GroupXY and set the Visible property to No and the Control Source to a function that will be created called GetGrpPages().

Function to Get Group Paging

Create another TextBox in the Footer called ReferToPage and set the Visible property to No and the Control Source to "=[Pages]". The ReferToPages text box forces the report to use the two-pass formatting when the report is printed. Without it, you would get Page 1 of 1 and then Page 2 of 2 for the "Outstanding" Performance Rating Group rather than Page 1 of 2 and Page 2 of 2.

Formatting Page Numbering

Create another TextBox with the Page of GroupXY data as the ControlSource as shown in the image. The Visible Property should be set to Yes.

Additional MS Access Page Numbering

The boxes should appear as in the image within the Footer in Design Mode.

MS Access Page Footer with Pages

Click on the Properties for the GroupFooter and set the Force New Page property to After Section.

MS Access Group Footer

In the GroupHeader section, click on the On Format Properfy and click on the ellipsis to create some VBA Code for the report.

MS Access Group Header

The Code for the Group Header will reset the Page field to Page 1 for each new Group.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
  Page = 1
End Sub

Add code to the top of the report declaring a Database and Recordset datatype. Write code for the Report Open Subprocedure to set the Database to the current database and run some SQL to delete the data in the table created earlier called Category Page Numbers and then add code to the Page Footer Section Format sub to iterate through the Groups at the open event of the report and populate the table with the Page Count for each Group. The code detects each new group and then pages through it and Updates the page number field with the current Page until it reaches the next group where it starts over with page 1 again.

Dim DB As Database
Dim GrpPages As Recordset

Private Sub Report_Open(Cancel As Integer)
 Set DB = DBEngine.Workspaces(0).Databases(0)
 DoCmd.SetWarnings False
 DoCmd.RunSQL "Delete * From [Category Page Numbers];"
 DoCmd.SetWarnings True
 Set GrpPages = DB.OpenRecordset("Category Page Numbers", DB_OPEN_TABLE)
 GrpPages.Index = "PrimaryKey"
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
 GrpPages.Seek "=", Me![PerformanceRating]
   If Not GrpPages.NoMatch Then
  'The group is already there.
    If GrpPages![Page Number] < Me.Page Then
    GrpPages.Edit
    GrpPages![Page Number] = Me.Page
    GrpPages.Update
   End If
  Else
   'This is the first page of the group. Therefore, add it.
    GrpPages.AddNew
    GrpPages![PerformanceRating] = Me![PerformanceRating]
    GrpPages![Page Number] = Me.Page
    GrpPages.Update
   End If
End Sub

Then type the code for the Function for the GroupXY textbox that is called GetGrpNames. This code

Function GetGrpPages()
 'Find the group name.  GrpPages.Seek "=", Me![PerformanceRating]
 If Not GrpPages.NoMatch Then
  GetGrpPages = GrpPages![Page Number]
 End If
End Function

By opening the table after opening the report in Print Preview, you can see that it has been populated with the correct values.

Populated Table for Groups in MS Access

Reviewing the groups, you can see that the pages are now grouped together for each group.

Page 1 of 2 in Group 1 MS Access

Page 2 of 2 in Group 1 MS Access

NOTE: For a datatype to be 'available' for Grouping or Sorting, it cannot be a LongText datatype. LongText in MS Access 2013 is the equivalent of the Memo type in Access 2010 and previous versions of Office.