This Access tutorial explains how to auto generate mass report by group, for example, auto generate reports by different department and then export to Excel.
You may also want to read:
Excel VBA filter value then copy data to new worksheet
Access VBA auto generate mass report by group to Excel
Assume that we have a staff list of 1000 employees in 20 departments, our goal is to export 20 staff list to Excel, one department for each staff list. There are several ways to achieve this task:
Approach 1
In Excel, apply Filter in Excel, then do 20 times of filtering of department and then copy the data to a new worksheet.
This is the most common method but is the slowest. It can be speed up by writing a Macro to do that but it can be complicated if different department requires different fields.
If you really want to do that you can read my previous posts how to use Autofilter and Copy Range in VBA.
Excel VBA filter value then copy data to new worksheet
Approach 2
In Access, create 20 Query for different departments and then export each Query as Excel.
If you need to customize each report for each department, I recommend you to take this approach but write a Macro to specify which 20 Query to output using DoCmd.OutputTo Method. I used this approach in my previous company where they have 70 department/team, and they have no generic rule to define the department and team grouping and even worse some reports require specific fields.
Anyway what I needed to do is to write 70 Query plus one Macro in the initial setup, then I only needed to rerun the Macro to generate all reports.
Approach 3
In Access, create a mapping table to define the employee grouping and then inner join the staff_list. It does not matter whether you group by Department ID, by Department Name or even by Employee ID, the purpose is to assign each individual employee an ascending Group Number. Finally export the report automatically as Excel for each group.
I recommend you to use this approach assume that the number of fields are the same across all reports. The only thing you need to do is to maintain the mapping table if there is any change.
Example of Approach 3
Assume that you have a table called [staff_list] as below
Create another mapping table [Dept_grouping]
Create a Query called [staff_list_with_grouping], inner join [staff_list] with [Dept_grouping] so that each employee has a Group Number.
Insert the below code in Module
Public Sub exportQuery() Dim qdf As QueryDef For i = 1 To DMax("[Group Number]", "[staff_list_with_grouping]") strSQL = "Select * FROM [staff_list_with_grouping] WHERE [Group Number]=" & i deptName = DLookup("[Department]", "[staff_list_with_grouping]", "[Group Number]=" & i) On Error Resume Next DoCmd.DeleteObject acQuery, "tempQry" On Error GoTo 0 Set qdf = CurrentDb.CreateQueryDef("tempQry", strSQL) DoCmd.OutputTo acOutputQuery, "tempQry", acFormatXLSX, "C:\test\" & deptName & ".xlsx", False Next DoCmd.DeleteObject acQuery, "tempQry" End Sub
What I am trying to do above is to create a loop from 1 to the maximum Group Number in [staff_list_with_grouping]. For each loop, I use that Group Number as a Query criteria for [staff_list_with_grouping] and then export the Query out to the folder.
Now three staff lists are generated in the test folder.
