How do I recursively split a table into several smaller ones?

I'm trying to find a rapid way to split, export, and possibly email a list of records based on location & manager fields.  I have a table in Access 2007 with each record's location, manager, and supervisor listed, as well as other data.  What I would like to accomplish is to have this data split into sections for each supervisor that contains their data, a section for the manager that contains all of their supervisors' data, and a section that contains all data for an entire site, each of which would then be exported into a copy of a template Excel 2003 file for some graphs & such to be automatically created around the data.

Right now I manually sort & filter the data, then copy and paste it into the Excel template and send it off to the respective supervisor, manager, or site manager.  There are around 2000 records a week, from a handful of sites, each with managers and supervisors.  I would like to automate this task.  I have attached a basic table with my layout in case I'm not making very much sense.
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can create Queries that you can use to Export to Excel. In your query, you would filter the data as need be (for example, you'd filter for Supervisor, the Manager, etc). You'd then use DoCmd.TransferSpreadsheet to do move the data to Excel (see help for more info on this topic).

JaysnAuthor Commented:
Is there any way to do this without creating an individual query for each file?  I'd rather not have to perform the maintenance needed for that method.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.