Unanswered: Create multiple Excel files based on grouping
I work in a hospital and am trying to create a query that would be grouped by patient number and then have all records that have that patient number be dumped into an Excel file (will create an excel file with the name of the patient #, but that's another problem altogether). I only know how to create 1 excel file with all patients and all records. So, if I have 5 patients, I would have 5 Excel files. I'm not sure if it can even be done, but any help would be appreciated.
You don't explain which method you intend to use for exporting the data to Excel. The following example uses the DoCmd.OutputTo method. It can be modified to use another method, such as Automation for instance.
The problem is that you need a query for each patient. This is because you cannot have Loop structures that would create a different subquery at each iteration in Access (not easily at least). Moreover, having a different query for each patient would mean that you would have to create a new query for each new patient.
The solution I propose here uses a VBA loop to modify the WHERE part of an existing query, then export it to Excel, for each patient.
1. Create a query (named Qry_Export_Patient in my example), like this:
Adapt it to the table or query containing the patients data (I used Mytable) and eventually replace * by the name of the columns you want to export.
2. Paste the following code in a new or existing independant module:
Public Function ExportToExcel()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strSQLSelect As String
Set dbs = CurrentDb
' Replace Qry_Export_Patient by the name of the real query.
Set qdf = dbs.QueryDefs("Qry_Export_Patient")
' Replace by the proper SELECT string from the basic query.
strSQLSelect = "SELECT * FROM MyTable"
' Replace the literal "SELECT..." string, using the proper names for the column and the table.
Set rst = dbs.OpenRecordset("SELECT Patient_ID FROM MyTable", dbOpenSnapshot)
Do Until .EOF
' Replace Patient_ID by the real name of the column if necessary.
' Note: If Patient_ID (whatever the real name can be) is not numeric, use:
' qdf.SQL = strSQLSelect & " WHERE Patient_ID = '" & rst!Patient_ID & "'"
qdf.SQL = strSQLSelect & " WHERE Patient_ID = " & rst!Patient_ID
' Here every Excel file is named <Patient_ID>.xls (e.g. 12345.xls),
' and is created in the default folder defined for Access data files. Change it if needed.
DoCmd.OutputTo acOutputQuery, "Qry_Export_Patient", acSpreadsheetTypeExcel7, !Patient_ID & ".xls"
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing