Combine more than one Access Database into one Excel Pivot

[swchoi89]

(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!

[CK76]

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.

[swchoi89]

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

Is the PowerQuery a paid tool?

[CK76]

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.

[AliGW]

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

[swchoi89]

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"??

[swchoi89]

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

Thank you!

[CK76]

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.

[swchoi89]

This worked perfectly, thank you so much for your help.

[CK76]

You are welcome and thanks for the rep.