Storing Data in MS Access and Querying it in Excel

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.

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.

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.