Access provides the following techniques you can use for filtering records:

Filter by Selection filters records based on selected data. First select a field or a part of a field, and then click the Filter by Selection button. Filter by Selection is additive, which means you can continue selecting values and pressing the Filter by Selection button. You could, for example, first filter the employees by London and then by Sales Representatives so you would see only employees who were Sales Representatives and who lived in London.

Filter by Form displays the Filter by Form window, which contains a blank version of the active form or datasheet where you can specify criteria for the filter (see Figure I- 17).

Apply Filter applies the filter you have specified in the active datasheet or form. When you apply a filter, the Apply Filter button becomes the Remove Filter button.

To start a new filter and clear all previous filter selections:

1. Open the Records menu.
    
2. Click the Filter command and then Advanced Filter/Sort on the submenu.
    
3. Click the Edit menu and then the Clear Grid command.
    
4. Close the Advanced Filter/Sort window.

Designing a Database

Access provides you with a great deal of flexibility and help in organizing your data. You can save yourself a lot of reorganizing and repetition, however, if you spend some time thinking about the data you want to include in your database.

Determining The Needs

The first thing you should do is a needs analysis to determine the kinds of reports or output you will want from your database. When you are designing the database, you should talk to all the people who will want reports from the database to determine just what reports they will want to see. You should also think about how you might set up the database so that there might be improvements over reports done at present.

When you know the information you want to be able to get from the database, you must decide if all the information needed for those reports is available, who has it, and how it will be entered.

Deciding On Tables

You should then divide the data into logical tables with each table containing data on only a single topic. It will help if you sketch the tables and their relationships before beginning.

Determining Fields

When you get down to deciding the fields for your tables, it is important that you break information down into the smallest units.

For example, you should break a person's name into last name, first name, middle initial, prefix, and suffix. That makes it possible for you to add Mr. or Ms. to a person's name for a mailing. It also allows you to add Jr., or 3d to a separate field so you could use the person's last name in a salutation without winding up with Dear Mr Smith 3d. A person's nickname is important as well if you want to be able to address a contact with the nickname; for instance, Joe rather than Joseph.

Addresses should also be broken into the smallest possible units. The city, state, postal code, and country should all be separate fields. When it comes to phone numbers, remember that today many individuals have office, home, second home, mobile, and fax numbers. In addition, you will need a field for E-mail addresses.

Defining A Primary Key

In each table you create, you must have one field that uniquely identifies a record. The power of Access or any relational database lies in its ability to find and bring together information stored in separate tables. To do this, each table should have a field or set of fields that uniquely identify each record stored in the table. This field or set of fields is called the primary key.

In a list of employees, it is possible for two individuals to have the very same first and last names. Access will need to have a unique piece of information about each of the records in the database. With an employee, a social security number provides a unique identifier, but in many tables, something like a social security number will not exist.

Access can assign a primary key for you if you have no unique identifier in your table. It will set up an ID field as your first field. Each time you add a record to the database, Access adds a consecutive number as the unique identifier for that record.

Creating The Database

Access provides you with two ways of creating a database. You can create a blank database and then add tables, forms, reports, and other objects, or you can use a Database Wizard.

Working With Wizards

Access wizards can be compared to an ATM machine. When you put your ATM card in the machine, the machine anticipates what you might like to do and walks you through the process very quickly.

The Access Database Wizard asks you questions about the database you want to create, lets you make some decisions about your needs, and then creates the required tables, forms, and reports for the type of database you chose. You can modify the database the wizard created to suit your needs, but a good bit of the up-front work is done for you.

Access wizards are also available to help you create tables, queries, forms, and reports.

Even professional database developers rely on Access wizards to do much of their work because wizards make many database chores so easy. If you ignore the Access wizards, you will only make more work for yourself.

If a Database Wizard does not exist for the database you want to create, you can create your own blank database and use wizards to create individual tables or reports.

When you use a wizard to create a database, Access can even add sample data to the database so you can begin learning to work with the database before entering your own data.

You can use the Cancel, Back, Next, and Finish command buttons to move through the wizard's steps.

Updated 01/21/03