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    
post Aug 13 2019, 11:10 AM

Posts: 130
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.

original item---,
                       |                 Level2item1---,
                       |                 |                      Level3Item1
                       |                 |                      Level3Item2
                       |                 Level2Item2---,
                       |                                        Level3Item1
                       |                                        Level3Item2
                                         |                      Level3Item1
                                         |                      Level3Item2
Go to the top of the page
post Aug 13 2019, 12:26 PM

Posts: 160
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
post Aug 13 2019, 12:31 PM

UtterAccess VIP
Posts: 10,957
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
post Aug 13 2019, 02:33 PM

Posts: 130
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    17th September 2019 - 09:53 AM