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

Yes, it would be better. There are many different paths you can choose from. Here's an option you may not have considered:

1. Open a new Access file
2. In that access file, make a link to the Excel file where your data is stored. (Go to Tables, right click and select "link tables")
3. Query as you need and copy/paste your results to the same XL file (but on a different tab) or to a second Excel file.

This way, you can leave your raw data in Excel if you're more comfortable with that, but still run SQL queries.

Regarding your second question, you don't need a book for that. In Excel, you can go to Data->"Import External Data"->"Import Data" and automatically pull data from your Access queries straight into Excel.

I hope this helps.

Edit: I also recommend that you google advanced Excel functions like Sumproduct, Sumif, and Countif. Depending on your analysis, these 3 functions may allow you to skip Access entirely.

Answer 2

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:

http://articles.techrepublic.com.com/5100-10878%5F11-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 3

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.

Answer 4

Certainly, you could create links to excel from within Access, and then your spreadsheets appear as if they were access tables. and you can do the data inseration, and querying and everything else, from Access. There's plenty of information, including step-by-step on how to link access to excel, if you google it.

Also, if you just want to insert data, you can try 3rd party tools, like Marie-Alix (currently given freely) that will open Excel. The customer service is really helpfull and will take the time to show you how its done.