ExtremeCPA - Technology for Business

Tuesday, July 05, 2005

First Steps w/ SQL (MS Access)

In a recent post, I discussed a way to use Excel's SUMIF and COUNTIF formula. As another reader asked therein, these formulas are not quite powerful enough to handle multiple criteria. My suggestion to him (and to you as well) was to use a SQL based solution such as Microsoft Access (or an open source alternative such as MySQL, PostgreSQL or SQLite). Note, however, that the open source alternatives while free, do require a larger investment of time and energy in learning the ins and outs of the software.

Quickly, you might be interested in using a SQL based solution when the data is structured (i.e., all columns and rows - no spaces per se, typically no "custom formatting", etc.). Direct extractions from systems such as SAP or Oracle are perfect, as those systems ARE databases themselves - and therefore produce data ready to be used by most other dB systems if you export them correctly (text delimited files). If you can get the data in this format, you can do some extremely powerful things with a database such as summing across categories by date for a very explicit set of circumstances (e.g., within a date range, for a specific sales region, for a particular set of customers, etc.).

Getting the data into Access is pretty simple, as long as your data is (again) in columns and rows with no "custom formatting". You'd simply create a new database (you have to create and save a dB before you can proceed), then go to the "Tables" tab (on the left) and finally hit "New | Import Table" ... the directions from there will be data specific, but if you've ever imported data into Excel it is very similar. If you have problems, feel free to email me and I will try to help.

Now that you have your data in the dB, you can go to the "Queries" tab (again, on the left) and hit "New | Design View". This will bring up a dialog asking you what tables you want to query. Choose the table you just imported, and then click "Close". In the grid, you can drag fields from the table to the grid to include them in the query. To filter (e.g. on specific dates), enter them into the "Criteria" section of the grid for the field you are querying on. If you want to group by (e.g., sales region), right-click anywhere in the grid and click on "Totals". This will bring up another available row in the grid ("Total:") where you can do "GROUP BY" queries or "SUM" queries, "AVG" queries, etc. Alternatively, you can use one of the "Wizards" instead of the data grid. Either way, it's much simpler than creating formulas in Excel once you get the hang of it, and much more powerful.

I know this was an overly simplistic post and you most likely have tons of questions. My suggestion would be to simply open up the program and try it out. Experimenting and using the online help system can take you very far. I'd be happy to write about specific questions, but I could go on for another 5 pages just on the basics - and I'm sure you don't want that now ;) Good luck!


Post a Comment

<< Home