Data mining with Access queries

Help desk professionals who support Office users need to be proficient in all of the Office Suite applications. If you've been putting off learning how to use Access, here's a crash course in how to query a table.

As a help desk analyst, you need to know as much as possible about the applications running on your end users' desktops. If your users run Microsoft Office, you've probably mastered a number of tricks for using and supporting Word, Excel, and PowerPoint. But how well do you know Access?

Fortunately, you don't have to be a Visual Basic developer to perform basic queries on your own tables or to set up user-friendly queries for your end users. I've created four quick lessons in how to mine your Access tables for the information you need.

Lesson 1: Filtering records by selection
For this lesson, I'll assume you know how to launch Access, open a database, and locate the table whose records you want to query. To illustrate how to perform basic data mining, I'll use the DemoData table shown in Figure A.

Figure A
We'll create queries to locate specific records in this sample table.

For beginning Access users, the best way to query a table is with the Filter By Selection tool. For instance, to view only the records for customers whose ZIP code is 10019, you can click on any record in the CustomerZip column that contains the entry 10019, and then click the Filter By Selection tool shown in Figure B.

Figure B
When you click on the Filter By Selection tool, Access will use the value in the currently selected field to filter the table records.

When you click Filter By Selection, Access will display only those records that contain 10019 in the CustomerZip column, as shown in Figure C. Note that the status line displays the word Filtered as a reminder that you're looking at a subset of all of the records in your table.

Figure C
Here's how the table looks after you clicked Filter By Selection. Click the Remove Filter tool (circled at top) to display all of the table records.

Lesson 2: Filtering on two or more fields
Of course, the Filter By Selection tool assumes that you can manually locate the appropriate value in one of your table rows. If you can't quickly locate a record that contains a value you want to use as a filter, try clicking the Filter By Form tool. (It's located between the Filter By Selection and Remove Filter tools.)

When you click Filter By Form, Access will display a single row containing all of the column names from your table. As you click in each column, a drop-down list will appear. Click on the drop-down list to display a list of unique values that appear in that column, and select the value you want to use to filter your records.

Figure D shows what the Filter By Form screen looks like when you click on the drop-down list for the CustomerZip column, select 10028 from the list, click on the drop-down list for ServiceCode, and selected A25. When you activate this filter, Access will only display records that have 10028 in the CustomerZip column and contain A25 in the ServiceCode field.

Figure D
The Filter By Form tool lets you design a query by selecting unique values from any of your table's columns.

To activate this filter, click the Apply Filter icon. Figure E shows the results of this Filter By Form operation. To deactivate this filter, you click Remove Filter just as before. (When a filter is active, the icon's name changes from Apply Filter to Remove Filter.)

Figure E
Here are the results of our Filter By Form.

Lesson 3: Saving and naming queries
The Filter By Selection and Filter By Form tools are easy to use for performing ad hoc queries on the fly. However, you and your users will need to run certain queries on a routine basis, especially if new records are frequently added to your tables. You won't want to create those queries from scratch every time you need them. Instead, save time by naming and saving your queries so you can rerun them with just a couple of mouse clicks.

To begin, open the Database window and click on Queries in the Objects pane. You'll see two options: Create Query In Design View and Create Query By Using Wizard. The wizard is an excellent tool, but we'll focus this lesson on creating a query in design view.

When you double-click Create Query In Design View, you'll see the Show Table dialog box. I'll assume you want to query a single table, so select that table and click the Add button. Access will then put a list of the table's fields in the new query design window as shown in Figure F. Click Close to dismiss the Show Table dialog box.

Figure F
Start by adding your table to the query design window.

To add columns from the table to the query grid, simply double-click on a column name. Once the columns have been added to the grid, you're ready to enter the values that Access will use to filter your records. Click on the Criteria row in the appropriate column, and enter the appropriate value. Figure G shows what the query grid looks like after you enter 10019 as the criteria for the CustomerZip column.

Figure G
Use the Criteria row to enter the values you want to use to filter your records.

After you enter the appropriate value or values in the Criteria row, click the Run button to execute the query. (The Run button is the tool with the red exclamation point.)

After Access displays the matching records, you'll notice something peculiar: The Run icon (the red exclamation point) disappears, and the Remove Filter icon will be grayed out. So how do you clear this query?

First, if you don't get the results you were hoping for, right-click on the query window and choose Query Design. Doing so will display the query grid, so you can modify the criterion and rerun it.

If the results are what you expected, close the query window. Access will then display a prompt asking if you want to save the design of this query. Click Yes. When the Save As dialog box appears, type a meaningful name for this query. Figure H shows what the Queries section of our Database window looks like after you save the sample query under the name The 10019 Query. Now, any time you want to filter your records using that particular query, all you have to do is double-click it.

Figure H
Here's what the saved query looks like in the Database window.

Lesson 4: Creating interactive queries
So far, I've looked at static queries, queries that always look for the same values when filtering the records in a table. For example, The 10019 Query always pulls records for customers whose ZIP code is 10019.

But suppose you want to be able to enter any ZIP code when you run that query? Fortunately, you don't have to redesign the query and hard code the ZIP code each time you want to search for records. Instead, you can createan interactive query that prompts you to enter the ZIP code.

To create a basic interactive query, you'll add the table and the columns to the query design like you did before. This time, however, instead of typing a literal string in the Criteria row, you'll type the text you want to appear for your prompt, enclosed in square brackets.

Figure I
You can force Access to ask the user for a value simply by entering the prompt in the Criteria row.

Figure I shows what this interactive query looks like. As you can see, the text in the query design grid, [Enter A Zip Code], is what appears in the prompt when you run the query. If you want your interactive query to prompt the user for values for two or more fields, simply enter additional prompts in the Criteria row of the other columns. By default, Access will select records that contain all of the values the user enters.

When you create this kind of prompt, the user must enter the appropriate data type. If the user enters a number when a string is expected, for example, an error will occur, and the user will have to rerun the query.

The query cat is out of the bag
These four quick lessons have only skimmed the surface of the kinds of queries you can run using Access. With practice, you can put these lessons to work when you need to filter a table on an ad hoc basis or create named queries that you can rerun at any time. In future columns, I'll show you some of the other data-mining options available in Microsoft Access, including advanced query techniques.

How many Access users do you support?
Do your users contact the help desk for answers to Access questions? To share your Access support tips, post a comment below or write to Jeff.

Jeff Davis has written over 1,000 technical and career-related articles for TechRepublic. He works as a freelance technical writer and database developer.

Editor's Picks