Linked Tables vs Linking to Data through Access

[kimmyd09]

I'm trying to figure out the best way to get my data into PowerPivot. I currently have all of my data in Access, where I then connect to it through Excel to update Pivot Tables. Now that I found PowerPivot, I'm thinking it would be easier to just use linked tables. However, often when I add new data in Access, I have to run a query to check for new data that is not defined in one of my other tables. For example, data in table A includes performance data with components C,D,E, and data in table B has info I define about the components (like C's region, for example). Then when a new component G is added to table A, I need to define component G in table B. Is there a way in PowerPivot to run a query to see that component G doesn't have anything defined yet for it in table B?

I'd like to completely do away with Access as the middle man between my reporting source and Excel, and solving this would be able to confirm that as a possibility. Thanks for your help!

 

[MD610]

Linked tables should be a last resort. They are very convenient for a quick, one-off analysis, or if you have a data table that never (or very rarely) needs to be updated but that is it. They are the worst option to manage for any type of long term solution that will require regular updates.

It would be a much better and more manageable idea to connect PowerPivot directly to the Access tables themselves or even better to the "reporting source" itself. I assume the "reporting source" must be some database and you are only using Access to query or shape the source data for analysis. Whatever the "reporting source", if you are pulling the data into Access, you can pull the data directly into PowerPivot instead.

You also might want to investigate Data Explorer which is a recent, free Add-In for Excel that MS has put out. It is basically a simplified yet powerful ETL tool for Excel/PowerPivot that can probably accomplish whatever you are doing with Access to your data before analyzing it.