StacyD
asked on
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_Rev
'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.
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_Rev
'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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That SO works! Thank you!
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
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
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°)
Ah, well. Some things you never learn. -- (^v°)
Well anyway thanks for the tip!
;-)
Jeff
;-)
Jeff
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