none
Combining Two Access Database to Pivot One Table

    Question

  • I run a set of Sales, Margin and Penetration reports for my department, and being at a sku level, this report will utilize more than 3 million records. I've used Access database interfacing with Excel to generate these reports, but I can't anymore since my Access database has maxed out at 2 GB.  I've installed Excel Power Pivot add-on hoping to create pivot table from multiple Access database, but can't seem to make it work.

    Since my one database is at 2GB, I was hoping to divide them into two, with exact same column heading.  When I've imported two sample database like that into power pivot, it keeps them on separate tabs, and when I pivot, the column headers, though, identical, are separate and I cannot pivot like it's from one database.  I've searched many times and cannot seem to find an answer.  Thanks to anyone who'd help!

     
    Tuesday, July 08, 2014 11:16 PM

Answers

  • Power Query has option to append and merge 2 data sets. 

    I think the easiest solution to your problem will be to pull data sets from different Access database using Power Query and then append them using in-build function for append available in Power Query.

    Thanks,

    Sagar K

    Wednesday, July 09, 2014 4:23 AM

All replies

  • Power Query has option to append and merge 2 data sets. 

    I think the easiest solution to your problem will be to pull data sets from different Access database using Power Query and then append them using in-build function for append available in Power Query.

    Thanks,

    Sagar K

    Wednesday, July 09, 2014 4:23 AM
  • Also in Power Query you have option to directly load data in Power Pivot.
    Wednesday, July 09, 2014 4:24 AM