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

Posted on 2009-02-11
Last Modified: 2013-11-28
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.
Question by:Jaysn
    LVL 84

    Accepted Solution

    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).


    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now