Geek Answers Handbook

Data storage in MS Access and query in Excel

I am currently participating in a non-IT project that currently has data that requires some systematic analysis (mathematical formulas). The data is currently stored in Excel, but it hurts to manually enter / massage the data into Excel for analysis.

  • Would it be better to store data in MS Access and use Excel to query Access? In other words, keep the data accessible and do the analysis in Excel. The ability to execute SQL queries on data will also simplify the analysis.

  • If so, do any of you have websites / books that describe how I will access Access from Excel?

+5
source share
3 answers

You are talking about access to enter and edit raw data. Then do your advanced calculations in Excel using Access, which will load the data.

I think the strategy is excel-ent because you are taking advantage of both applications.

Regarding the Access query from Excel, this page contains detailed, clear instructions:

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

I found that one of Google did a search for "access request to Excel." This was the first link; check others if you want more information.

+3
source

, Access. SQL , . , Excel , , . Access, Jet ( , ).

Excel 2003 . http://dicks-clicks.com/excel/ExternalData.htm

, SQL, QueryManager http://www.jkp-ads.com/download.asp , MSQuery.

+2

, . , . , , , :

  • Excel, . ( , " " ).
  • , , / XL ( ) Excel.

, Excel, , SQL-.

, . Excel Data → " " → " " Access Excel.

, .

Edit: I also recommend advanced Excel features like Sumproduct, Sumif, and Countif. Depending on your analysis, these 3 functions may allow you to completely skip access.

+1
source

More articles:


All Articles