Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

Export Pivot results to persisted table

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.
0
StacyD
Asked:
StacyD
  • 3
  • 2
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
harfangCommented:
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°)
0
 
StacyDAuthor Commented:
That SO works! Thank you!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanMIS LiasonCommented:
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

0
 
harfangCommented:
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°)
0
 
Jeffrey CoachmanMIS LiasonCommented:
Well anyway thanks for the tip!
;-)

Jeff
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now