+ Reply to Thread
Results 1 to 10 of 10

Combine more than one Access Database into one Excel Pivot

  1. #1
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    46

    Combine more than one Access Database into one Excel Pivot

    (Complete Access noob here)

    Hi guys!

    I inherited a task where I have 15 CSV files that are about 4 million records large. When I combine them into smaller number of files using Access, I understand that I cannot have more than 2 GB worth of data in a single database. So I have been creating three separate Access Databases and linking them to an Excel file pivot table.

    Now the question is whether I can have the three separate Access databases feed into a single pivot table, as opposed to having three separate pivots? The Access files in question have the exact same # of columns & its headers. The only difference is the content of the files.

    Thanks in advance!

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,619

    Re: Combine more than one Access Database into one Excel Pivot

    This largely depends on your Excel version.

    Are you still using Excel 2007? Or are you using later version?

    If latter, you should update your profile. You can use PowerQuery (Get & transform) to Append (Union) tables into single data set. Process will depend on exact version and/or tools available.

    If still using Excel 2007... you may be able to use MS Query to create Union query and load it to data model. Though I can't be certain, since I no longer have access to legacy MS Query on my version.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    46

    Re: Combine more than one Access Database into one Excel Pivot

    Ooops, I'll update. I am using the most recent version (Office 365).

    Is the PowerQuery a paid tool?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,619

    Re: Combine more than one Access Database into one Excel Pivot

    Power Query is now called "Get & Transform" in Office 365 and comes standard.

    I'd query each MS Access separately (as connection only). Then in Query Editor, use "Append" tool to combine data.
    Then using data option, load combined query directly to data model. Then use Power Pivot to report on data (Power Pivot also comes standard for Office 365 as of version released last year).

    This method uses vertipaq engine to compress data and is very efficient at compressing vertical (many rows) data.
    Last edited by CK76; 07-02-2020 at 01:39 PM. Reason: For clarity

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,086

    Re: Combine more than one Access Database into one Excel Pivot

    No, it's a standard part of your version - Get & Transform on the Data ribbon.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    46

    Re: Combine more than one Access Database into one Excel Pivot

    Ok, let me give it a go.

    When I add them to the query, I always choose the "Load To", then select Connections Only with "Add to Data Model". Is there a difference if I don't "Add to Data Model"??
    Last edited by AliGW; 07-02-2020 at 02:40 PM. Reason: Please don't quote unnecessarily!

  7. #7
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    46

    Re: Combine more than one Access Database into one Excel Pivot

    Quote Originally Posted by AliGW View Post
    No, it's a standard part of your version - Get & Transform on the Data ribbon.
    Thank you!

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,619

    Re: Combine more than one Access Database into one Excel Pivot

    Is there a difference if I don't "Add to Data Model"??
    Yes there is.

    In PowerQuery connection is basically just that, it defines the connection string to get the data and what transformation steps should be applied. But in itself, does not contain data.

    So, by specifying connection only in each query, you are not wasting resource by loading data at each stage. However, by marking it as "Add to data model" each of the query marked as such will load data into model and will cause data bloat.

    You'll only need to check "Add to data model" for the single combined query.

  9. #9
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    46

    Re: Combine more than one Access Database into one Excel Pivot

    This worked perfectly, thank you so much for your help.
    Last edited by AliGW; 07-02-2020 at 02:39 PM. Reason: Please don't quote unnecessarily!

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,619

    Re: Combine more than one Access Database into one Excel Pivot

    You are welcome and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Power Pivot Using Access Database Files
    By thanhie in forum Excel General
    Replies: 1
    Last Post: 10-21-2019, 02:52 AM
  2. List of Access database paths in Excel - Need to return Access version
    By Wedge120 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2019, 12:19 PM
  3. Replies: 2
    Last Post: 07-05-2013, 09:37 AM
  4. Replies: 1
    Last Post: 07-26-2006, 01:45 PM
  5. [SOLVED] Create Pivot from access database by VBA
    By ST in forum Excel General
    Replies: 0
    Last Post: 07-25-2006, 11:35 PM
  6. Excel query of Access database - changed database filename, now error
    By ucdcrush@gmail.com in forum Excel General
    Replies: 2
    Last Post: 03-08-2006, 04:45 PM
  7. [SOLVED] Pivot Table against an Access Database
    By Richard in forum Excel General
    Replies: 1
    Last Post: 02-27-2005, 12:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1