Microsoft Access Tips for Casual Users

Provided by Allen Browne.  Created: July 2007.  Last update: August 2008.

Don't use Yes/No fields to store preferences

A common mistake is to create heaps of Yes/No fields in a table to store people's preferences. This article explains how and why you should use a relational design instead.

A sports teacher might set up a matrix to record students' interest in various sports like this:

Student	Basketball	Football	Baseball	Tennis
Josh
Mark
Mary-Anne
Olivier
Trevor

If the teacher knows nothing about databases, he will create a table with a Text field (for the student name) and a bunch of Yes/No fields so he can tick the sports the student enrols in.

Paper forms are laid out like that, so lots of people make the mistake of building database tables like that too — the diseases a patient has had, the newsletters a client wants, the classes someone will attend, the answers to a survey - all built as tables with many check boxes.

Do not build a table like that! A Yes/No field for each possible choice is not the way to build a database.

Thinking relationally

A major problem with these repeating Yes/No fields is that you must redesign your database every time you add a new choice. To add Netball, the teacher must create another Yes/No field in the table. Then he must modify the queries, forms, reports, and any code or macros that handle these fields. A relational design would avoid this maintenance nightmare.

Thinking relationally, we have two things to consider: students, and sports. One student can be in many sports. One sport can have many students. Therefore we have a many-to-many relation between students and sports.

A many-to-many is resolved by using three tables:

• Student table (one record per student), with fields:
      - StudentID       AutoNumber
      - Surname        Text
      - FirstName      Text
• Sport table (one record per sport), with fields:
      - SportID          AutoNumber
      - Sport             Text
• StudentSport table (one record per preference) with fields:
      - StudentSportID  AutoNumber
      - StudentID          Number        Relates to Student.StudentID
      - SportID              Number        Relates to Sport.SportID

The third table holds the preferences. If Josh is interested in two sports, he has two records in the StudentSport table.

This relational structure copes with any number of sports, without needing to redesign the tables. Just add a new record to the Sport table, and the database works without changing all queries, forms, reports, macros, and code.

You can also create much more powerful queries: there is only one field to examine to find the sports that match a student (i.e. the SportID field in the StudentSport table.)

Interfacing this design

Create a form bound the the Student table. It has a subform bound to the StudentSport table. The subform has a combo for selecting the sport, and you add as many rows as you need for that student's sports.

When you add a new sport to the Sport table, it turns up in the combo box automatically. You can therefore choose it without needing any changes.

Taking it further

The same principle applies to many types of database, e.g.:

• patients and diseases (sample below)
• clients and newsletters
• people and preferences

Questionnaires are another area where this applies. Duane Hookom has a sample database called At Your Survey to illustrate now to handle those.

Suggestions for enhancing the usability of this kind of database:

1. Download a sample database (20kb zipped, for Access 2000 or later) illustrating the tables and forms to handle people and diseases.
2. To quickly jump to the record for a student, add an unbound combo to the header of the form. Details in Using a Combo Box to Find Records.
3. To filter the main form to only those students associated with a particular sport, see Filter a Form on a Field in a Subform.

For further reading see Normalizing Data (PDF 90kb) or Database Design 101 links.