[ Go to September 1997 Table of Contents ]

Applications /
Jim Boyce

Get a Handle on Your Records

What do you think of when somebody says Microsoft Office? For most people, Word and Excel come to mind first because word processing and spreadsheets occupy so much of their day. There are some, however, who think of Microsoft Access, since it plays an important role by helping them organize inventory, customer and other information. If you're not familiar with Access, you may be surprised by what you're missing.

Plugging data into a database merely lays the groundwork for all the information you'll be able to glean later. Once you learn how to query that data, your database will become truly useful. That's when the data in an Access database becomes something more than just a table of names and numbers.

Setting tables

The table is Access' basic logical component, since you use it to store data. A table looks a lot like a spreadsheet, but the structure is quite different and more ordered. The rows in a table represent database records. A record is a collection of related data items. For example, in an inventory database each record represents a single product and contains multiple fields, each with a different type of information about the product. One field stores the product name, another the quantity on hand, another the cost and so on. The columns in the data table store the fields for each data record. You can read all the information for a record by starting at the left side of the row and reading to the right.

Poring over a data table is fine if you're working with a small amount of data or only a few fields, but it's not a practical way to deal with the complex databases you're bound to use in the real world. What's more, you're not putting the power of your computer to good use unless you're ordering that data through filters and queries.

One of the easiest ways to pull information from an Access database is to filter the data table. Filtering lets you focus on a particular aspect or field, hiding from view those records that don't fit your filter criteria. Filtering lets you see the individual trees in the forest. You tell Access which types of trees you want to see, and the rest of the forest simply goes away, leaving the ones you selected standing in plain sight.

The easiest way to filter a table is by using the Filter By Selection feature. Simply click on a record field that represents the type of record you want to view, then choose Records/Filter/Filter By Selection. Access then displays only those records that contain field data matching the selected field, hiding all other records. Using the Northwinds sample database that comes with Access, you might want to view only the products provided by a specific company. (If you installed the database during Setup, it's located in the Office/Samples folder under the main Microsoft Office folder.)

After opening the Products table in Access, click on a company name in the Supplier column, then choose Records/Filter/Filter By Selection. Access will show only those records that contain the selected supplier name in the Supplier field. To view the entire database again, choose Records/Remove Filter/Sort.

Filter by exclusion

You can also filter a data table by exclusion. Instead of viewing records that contain the selected data, you view the records that do not contain the selected data. In the Products table, for example, you might want to view everything except records for condiments. So, you click on any one instance of Condiment in the Category column, then choose Records/Filter/Filter Excluding Selection. Access then hides the records that contain the selected data, in this case records for condiments.

Access lets you create more complex filters, too. To do so, open the table you want to filter and choose Records/Filter/Advanced Filter/Sort. Access opens a new filter window containing two panes. The top pane, called the Table Pane, contains a box that lists all the fields in the table. Use this box to select the fields to include in the filter criteria. The bottom pane, the Criteria Pane, contains all the fields and criteria that make up the filter.

To define the filter criteria, double-click on the field you want included in the filter criteria, or drag it from the top pane to the bottom pane. Then, you need to apply criteria operators to the filter. For example, using the Products table, you might include the Category field in the filter and apply the criteria <>>"Condiments" to filter out products that have "Condiment" in the Category field. Or, you might drag the Unit Price field to the Criteria pane then add the criteria 10 to filter out all products that have a unit price of $10 or less. By combining multiple criteria, you can easily refine the view to include only the data you need.

Creating a query is similar to applying a filter, because a query lets you selectively view data based on various criteria. But while a filter simply derives a different view of the data, a query actually produces a new data set-called a dynaset-by extracting records from the database.

The dynaset looks much like a new data table, and you can perform many of the same functions on a dynaset that you can perform on a table. The dynaset reflects changes to data in the table. Another important difference: A filter is lost when you close the table, but you can save a query and run it again at any time.

You create a query object in much the same way that you build a data table. A newly created query is empty, and you must add fields to it. Rather than creating fields from scratch as you do for a table, you bring in the fields from an existing data table. Then you add records to the query by defining the criteria for their inclusion, the way you do when creating a filter.

To create a new query object, select the Database sheet and click on the Queries tab. The Queries page shows all existing queries. To view an existing query, double-click on the query or select it and click on Open. To create a new query, click on New. Access opens a dialog box in which you can choose from several different methods and wizards for creating the query. I'll concentrate on the Design View method of creating a query. To do that, choose Design View from the dialog box and click on OK.

The Show Table sheet appears next. From the Tables page, select the tables in the database that you want to include in the query and click on Add to add them to the query. As you add a table, a box for the table appears in the query window's top pane. You use this box later to specify the criteria for the search.

The Query window consists of two panes: The Table pane at the top contains objects for each of the tables in the query, and the Query By Example (QBE) grid in the bottom pane references the method you use to define the query. You design the way you want each dynaset record to appear, creating an example. The query then fills in the dynaset accordingly.

Query creation

Creating a query is similar to creating a filter. You drag fields from the Table pane to columns in the QBE grid in the bottom pane. Let's assume you want to query the Products table to find all records for products that are not discontinued and that cost more than $10. You would drag the ProductName, UnitPrice and Discontinued fields to the QBE grid.

>Then you must specify the criteria for each field to apply to the query. To do so, click on the Criteria cell for the field and type the criteria. For example, click on the Criteria cell for the UnitPrice field and enter the criteria 10. In the Criteria cell for the Discontinued field, enter False. Leave the Criteria cell for the ProductName field blank in order to include all product name values. When you've finished defining the query criteria, choose Query/Run. Access runs the query and creates a dynaset of the data based on the query criteria.

The filtering and querying techniques I've covered this month are fairly simple yet powerful in their ability to help you sift through and analyze a database. With these techniques, you're well on your way to getting a handle on your database.

Access also offers more complex features, such as the ability to publish to the Web, create hyperlinks to other resources, include calculations in queries, add or remove records through queries, and much more. I'll cover those in future columns.

Contributing editor Jim Boyce is the author of Upgrading PCs Illustrated (Que, 1997). Contact Jim in WINDOWS Magazine's areas on America Online and CompuServe, or care of the editor at the addresses on page 20.

Windows Magazine, September 1997, page 263.

[ Go to September 1997 Table of Contents ]