We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Export Pivot results to persisted table

StacyD
StacyD asked
on
Medium Priority
415 Views
Last Modified: 2013-11-27
Hello:
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]
'FROM Ann_PIVOT_BASE
'GROUP BY Ann_PIVOT_BASE.[EmpID], Ann_PIVOT_BASE.[level II category]
'PIVOT Ann_PIVOT_BASE.Date

I want to send the results to a table called Annualized_Master. I will drop and recreate this table each time.
Comment
Watch Question

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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.

JeffCoachman
Commented:
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;

Cheers!
(°v°)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
That SO works! Thank you!
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
harfang,

Another tool in my toolbag!
Thanks

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

Thanks again

Jeff

Commented:
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°)
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Well anyway thanks for the tip!
;-)

Jeff
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.