MS Access Databases: Data Mining Through Queries

Without the trusty query, our database would be as unwieldy as an overstuffed spreadsheet table. We would not be able to see a group of “trees,” because the “forest” of information would spread wide and deep. We need the power of queries to bring order and focus to our inventory of hard-earned data.

The new database user easily learns how to do quick filters in a form or a table. (A “filter” is a look at just one specified category of information, e.g., all customers who purchased less than a specified amount). Clicking on the “advanced filter” quickly either in the form or table view allows the user to “drill” into the data to find specified information. A filter is really a sort of "query on the fly" for a quick look at limited parts of the database.

The problem with repeatedly using filters in a database is the same as that encountered in large spreadsheets. Soon, either the user forgets the combination of filters that resulted in the “ad hoc” view, or the process becomes even more cumbersome as the filters are refined. It is akin to web surfing, where we can rarely remember how we got to the website for which we were searching. So what we do at that point is make bookmark.

A saved query in an MS Access database is like the web bookmark. In a saved query, we have instructed Access to reserve a specific view of the information either in a table or a form designed especially for the query. Database managers frequently use queries for special form views so that data entry people can only see, use or edit part of the database.

It is important to remember that a query is nothing more (or less) than a restricted view of the big data table. Any changes made to the big table are also reflected in the query table and objects (forms and reports) that depend on the query. Queries, in other words, are restricted, or truncated, view of the overall database.

Finally, we have three queries that are work savers, but take a little practice:

1. The crosstab query. This is the MS Access equivalent of the Excel pivot table. It comes in handy for counting and information that is buried in fields. For example, you would use a crosstab query to count or total the number of sales within a specific sales region or zip code.

2. Make-table query. Say you wanted to export some specific information from a large database so that the user could either begin a new set of data or use the table for a special purpose in a spreadsheet. Use the make-table query export the new table for you.

3. Append query. Use the append query to delete old records that need to be archived into a separate database. As your database grows, the append query is a handy tool to keep your database from becoming overloaded with old information, while at the same time archiving your important business records, which the IRS could demand for previous years.

The power of the MS Access database is not always in its robust ability to hold tons of data. It is through its ability to “drill down” so that we can become true “data miners.”