Creating the Correspondence Table in Access 

Double-click on Create table in Design View to get a screen for defining the elements of our table. Your screen is blank; I've defined the fields in the figure below to aid our discussion of how to define the table.
 

As you can see, each data element of the table has been defined by giving it a Field Name and defining its Data Type. Access (or any other DBMS) has several types of predefined data types that you can use to define how a field will accept and store data. A Text field will accept any type of typed-in data (letters, numbers and punctuation). A Number field will only accept numeric data. A Date/Time field will accept data formatted as dates and/or times.

With many other DBMSs, you would have to be concerned about defining the length of each field. That's not necessary with Access, unless you wanted to restrict the length of a field as a safeguard against improper data being entered.

Note that I've added a field called IDNumber. This is to provide a primary key, or unique identifier for each record. Access doesn't require every table to have a primary key field, but it is usually good design to provide one. The Data Type for IDNumber is AutoNumber, which is a special field type maintained by Access. Any new record added to the table will automatically be assigned an IDNumber value that is one unit higher than the last record added. The first record will have an IDNumber of 1, the second record 2, and so forth.

The name of the field can be any descriptive name. Spaces are allowed, but it's probably a good idea to not have them in a field's name. If you get deep into Access development, you will find that not having spaces in a field name simplifies some processing.

The Description area is for documentation. This information is not used for processing; it's just sometimes useful to give yourself little reminders of the purpose and format of a field.

So go on and define the fields as shown in the figure above.

Note that as you define each field, the lower half of the screen provides additional options for specifying the characteristics of the field. The figure above shows that we have taken advantage of some of these options to add further specifications about the CorrespondenceForm field. Data in this field should have only one of two possible values; "Letter" or "Email." What we've done is specified that the Display Control for this field will be a "Combo Box." In other words, whenever this field is displayed for entry of data, it will be displayed as a combo box, which looks like this:

A combo box (also known as a drop-down box or menu) provides the user with a predefined list of valid data values from which they can choose. This helps ensure consistency of data entry. By defining this field this way, we avoid the user entering data inconsistently. If we want the user to enter "Email," we don't them entering things like "email" or "e-mail" in this field; this could result in questionable output. As the image above shows, we have specified the values for this combo box to be "Letter" or "Email."

What other fields in this table should have data entry controlled with a combo box? Consider:

• AssignedTo - If there is a finite number of staff persons to handle these matters, you can put their initials into this combo box.
• Category - If you can accurately and completely categorize the kinds of letters a CEO might get, consider making this field a combo box.
• ResponseForm - Same argument as for CorrespondenceForm.
• Closed - You can restrict entry into this field to "Yes" or "No." Note: Access and other DBMSs maintain a "Logical" (in Access terms "Yes/No) field type that serves the same purpose, but sometimes processing can get tricky with that kind of field.

In this example database, the abovementioned four fields as well as the CorrespondenceForm field have had their display control set to combo box.

The lower part of the screen has additional things you can use to further customize the definitions of the data elements. We won't go into detail on them here, but you are welcome to explore how they work. Be sure to make use of Access' extensive help facilities for explanations.

Before you finish, you should define IDNumber as the primary key. Right-click on the grey box next to IDNumber to bring up a small menu. Click on the Primary Key item to define IDNumber as the primary key for this table. You should now see a small key in the grey box, indicating that IDNumber is the primary key for this table.

Save the table by clicking on File then Save. Let's call this the Correspondence table.

You can now close this operation by clicking on the X at the upper right corner of the table definition panel.

Good start!

Next: Creating the data entry form.