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.
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.
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.
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.
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.
Finally, run the report to see how it looks in Print Preview. Notice that it looks very nice, but it needs Page Numbers.
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.
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.
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().
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.
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.
The boxes should appear as in the image within the Footer in Design Mode.
Click on the Properties for the GroupFooter and set the Force New Page property to After Section.
In the GroupHeader section, click on the On Format Properfy and click on the ellipsis to create some VBA Code for the report.
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.
Reviewing the groups, you can see that the pages are now grouped together for each group.
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.