UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dissplay/export As Pivot Table, Access 2016    
 
   
JAchord
post Aug 13 2019, 11:10 AM
Post#1



Posts: 97
Joined: 11-July 14



Is there anyway to export a query and have it formatted as a pivot table in excel? I have a bill of material query that shows three levels. So it would be like the example in the code box. I can easily make a form or report to display like this but I need it to be exported to Excel for manipulation to be imported in a different program. I know that pivot tables were removed after 2010 but is there anyway to simulate it.

CODE
original item---,
                       Subitem1---,
                       |                 Level2item1---,
                       |                 |                      Level3Item1
                       |                 |                      Level3Item2
                       |                 Level2Item2---,
                       |                                        Level3Item1
                       |                                        Level3Item2
                       |
                       SubItem2---,
                                         Level2item1---,
                                         |                      Level3Item1
                                         |                      Level3Item2
                                         Level2Item2---,
                                                                Level3Item1
                                                                Level3Item2
Go to the top of the page
 
dale.fye
post Aug 13 2019, 12:26 PM
Post#2



Posts: 134
Joined: 28-March 18
From: Virginia


depends on what your data looks like. Is your table self-referencing (heirarchical)?

Getting it to export as you have shown would be difficult, but to get something like:

original item---, Subitem1---, Level2item1---, Level3Item1
original item---, Subitem1---, Level2item1---, Level3Item2
original item---, Subitem1---, Level2item2---, Level3Item1
original item---, Subitem1---, Level2item2---, Level3Item2

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
projecttoday
post Aug 13 2019, 12:31 PM
Post#3


UtterAccess VIP
Posts: 10,865
Joined: 10-February 04
From: South Charleston, WV


Sure. But you have to code it. Your code exports the data and then creates a pivot table on the spreadsheet.

--------------------
Robert Crouser
Go to the top of the page
 
JAchord
post Aug 13 2019, 02:33 PM
Post#4



Posts: 97
Joined: 11-July 14



Yes the data is self-referencing. I had to use three queries. The first finds the first item based on the original item Key, the second then finds the second item set based on the item keys from the first query, and the third finds the item set based on the keys from the second query. I guess in theory I could keep making queries until no results were found. I guess another option would be to export all three queries to one Excel file but each with its own sheet. I guess it would be better to have each original item with its own sheet.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 02:47 AM