Export Pivot results to persisted table

Posted on 2009-02-11
Last Modified: 2013-11-27
I want to persist the results of a pivot table query. I want to be able to use the outputto {or if you have a better method ok}. Since you cannot create a table in the same statement as a transform, I tried to export it to an xls and then import the data as a table. But the result is > 65K rows. Is there a way to use docmd.outputto to send results directllt to a table? Here is teh query:

'this is the sql from the pivot qry named Ann_Pivot
'TRANSFORM Sum(Ann_PIVOT_BASE.YTD_Revenue) AS SumOfRevenue
'SELECT Ann_PIVOT_BASE.[Emp ID], Ann_PIVOT_BASE.[level II category]
'GROUP BY Ann_PIVOT_BASE.[EmpID], Ann_PIVOT_BASE.[level II category]

I want to send the results to a table called Annualized_Master. I will drop and recreate this table each time.
Question by:StacyD
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    The SQL you have posted is I believe a "Crosstab" query, not really a "Pivot Table" query.

    A basic Pivot table query is just a standard query, the "Transform" is done behind the scenes.
    The Pivot table is just a "View" of the query, used to easilly "Pivot" the fields, and view summaries.

    Try this.

    Open the query in design view.
    Click: Query-->MakeTable query
    Name the tabls and run the query.

    See if the resulting table is what you were after.

    LVL 58

    Accepted Solution

    As you found out, in Access, a query cannot be a cross-tab and a make-table query at the same time. Nor can you use a cross-tab as subquery in a FROM clause.

    You will need to create a new query in order to save the cross-tab to a table:

        SELECT * INTO Annualized_Master FROM Ann_Pivot;


    Author Closing Comment

    That SO works! Thank you!
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Another tool in my toolbag!

    So why doesn't simply changing the Crosstab query to a Make Table query do this as well?
    As you know, as soon as you click Query-->Make Table, it immediately changes it to a GroupBy query.

    Thanks again


    LVL 58

    Expert Comment

    Thanks, Jeff! As it stands, I tried this very afternoon, once again, to turn a cross-tab into a make-table. Bites me every time (and how many times have I tried to include a cross-tab as sub-query -- quite illogical if you think about it, but it would be so convenient).

    Ah, well. Some things you never learn. -- (^v°)
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Well anyway thanks for the tip!


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    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…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    734 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

    25 Experts available now in Live!

    Get 1:1 Help Now