Storing Data in MS Access and Querying it in Excel

Question

I'm currently on a non-IT project that currently has data which requires some systematic analysis (mathematical formulas). The data is currently stored in Excel but it's a pain to manually enter/massage data in Excel to do the analysis.

1. Would it be better to store the data in MS Access and use Excel to query Access? In other words, store the data in access and do the analysis in Excel. Being able to do SQL queries on the data would also simplify the analysis.

2. If so, do any of you have websites/books that describes how I would go about querying Access from Excel?

Answer 1

You're talking about Access for entry and editing of the raw data. Then doing your advanced computations in Excel with Access feeding it the data.

I think that's an Excel-ent strategy because you're taking advantage of the strengths of both applications.

As to how to query Access from Excel, this page provides detailed clear instructions:

https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-6112813.html

I found that one from Google by searching for "excel query access". That was the first link; check the others if you want more information.

Answer 2

I think there are a lot of advantages to storing the data in Access. If getting at the data with SQL will be helpful, then it's a no-brainer. Other than that, you can store the data in Excel just as if it was in a database, but it will be up to you to enforce normalization and data integrity. If you put it in Access, Jet will force you to do it (assuming you set it up properly).

For Excel 2003 and prior, I have a page with lots of pictures. http://dicks-clicks.com/excel/ExternalData.htm

Also, if you're comfortable entering your own SQL, do yourself a favor and download QueryManager from here http://www.jkp-ads.com/download.asp It will allow you to edit your queries much faster than using MSQuery.