This exercise will step you through the process of creating a database in Access. For the purposes of this exercise we will concentrate on building three tables in a relational database for a library.

1. From the Windows desktop, click on Applications.

2. Click on Databases.

3. Click on Access.

4. Click on "Create New Database using : blank database", then click on OK.

5. Access will immediately want to determine the name of the database and where it is to be located. Be sure to change the directory at the top of the Save small screen to the correct drive:, and then name the database "your name" at the bottom of the small screen. Then click on Create.

Create Tables:

Note: When you are done with this step, you should have created tables for 3 entities. I have attached several reference tables to help you fill in the blanks as you work through the steps below.

To create a new table:

Click on New.

Access will give you several options. Click on Design View, then OK.

For each attribute (field) in the entity (table) enter the field name, data type and description columns.

Tab over to the data type column. The data type defaults to text. You can change this to another data type by clicking on the pull-down menu. See the attached table of data types to set the appropriate data type for each field. For embedded dashes like those in ISBN, you will want to use a text field. Data type Number should only be used for fields to be used in mathematical calculations.

Tab over to the description column and type a valid description. Tab again and it takes you to the next field name entry. Continue to enter field names, data types and descriptions until you have finished the table.

To create a primary key for a table:

Recall that each entity has to have a unique identifier, and that each entity relates to a table in the Access database. You now need to indicate to Access which of the fields you have created will serve as the primary key for the table. When choosing the fields to use as the primary key, remember that the primary key must be different for each instance or row in the table.

1. Click on the field that is the unique identifier for the entity.
2. Then click on the Primary Key icon (it looks like a little gold key). You should now see a small key in the far left column next to the field name for the field you have designated as the key.
3. If you have made a mistake and click on the wrong field for the primary key, you can always turn it back off by following the same set of steps. The primary key field is a toggled indicator. If you click on the key the first time it becomes a primary key, then clicking on it a second time will turn the indicator off.
4. If you do not indicate a primary key for each table, Access will add a field and make it the primary key. It is always better for the designer to control the way things are set in the database, so be sure to establish your own key fields.
5. Only certain field types can be keys. Text, number, date/time, and AutoNumber fields all qualify for primary key status.

Now we are ready to talk about specific field properties that will help to display data in a proper format, and which will help to prevent entry of bad data. This discussion will cover some of the basic field properties. For a more complete discussion of field properties, I suggest you read Access 7.0 by O'Leary and O'Leary, or Access for Dummies by Kaufeld.

Click on the first field in your table, and look at the field properties at the bottom on the screen.

Format:

• Formats change the way you see data on the screen or in query reports. Formats do not affect the way data is physically stored in the database.
• The best way to explain formats is with an example: The most common format for a telephone number is (000)000-0000;0. The zeroes indicate that numeric characters are required, and that the parentheses and hyphen will be displayed even if only the numeric digits in the phone number are required. Try entering formats for the ISBN and any other fields you feel are appropriate. Use the attached table of commonly used formats for hints.

Input Mask:

• An input mask is a filter which will allow only certain types of data to be entered in a particular field.
• For example, an input mask could be used to force state abbreviations to be all capitals, even when they were not entered that way. The input mask for such a field would be >.
• Add input masks to your tables where appropriate. Refer to the attached table for commonly used input masks for examples.

Default Value:

• A default value is a value which is automatically entered into the field when the user fails to enter anything. This value is actually put in the field before the user enters anything in that line of the table.
• Enter default values you feel would be appropriate for these three tables. One that particularly comes to mind is a default of 21 days for the number of days a book may be borrowed.

Validation Rule:

• The validation rules are used to check the data entered in the field to see that it meets edit criteria.
• These rules are written with Boolean Algebra operators.
• Enter appropriate validation rules for your table. I have attached a table of commonly used validation rules for you to use.

Validation Text:

• The validation text is the message that will be displayed on the screen when the data entered do not meet the validation rule requirements.

Indexed:

• This indicator tells Access whether or not you will be using this field as a search key during some query or report.
• The indicator has 3 values, No, Yes (with duplicates), and Yes(No duplicates).
• Any field in a table can be indexed.
• Key fields are automatically indexed.
• Indexes make queries, sorts and searches faster, but they also add overhead to processing. Therefore, you do not want to mark any fields as indexes which will not be used as indexes later.

Better safe than sorry. Save the table as often as possible. Click on the Save icon, or click on File and then on Save.

To enter data, click on View, then Data Sheet. You can now enter your data. Enter a few records to test the validation, input mask and formats to see if they are set the way you want. (I have attached sample tables of data for the Library database. You should try entering that data and then add some of your own if you would like.)

While entering data, purposely enter bad data in fields with validation rules and see what happens. Enter the ISBN without the hyphens, and see how the system fills them in for you. If these things are not working properly, go back to the design view of the table and make corrections. Then try again.

Save the table again after you have entered the data

Close the table and go back to create tables and enter data for the other two tables. (You will need all three tables to complete this exercise and be ready or tomorrow.)

Click on the relationship icon. The relationship screen will appear. (The example relationships screen is for the Northwind database.)

Double click on each of you tables in your database to bring them onto the relationship screen. If the list of tables does not appear, then click on Relationships, then on Show Table, to bring up the list. Once you've brought up all the tables, click on close on the Show Table screen.

To establish a relationship simply click on ISBN in one table. Holding the left mouse button down, drag ISBN over to ISBN in another table and drop it there. This will create a relationship between two tables using the ISBN key. Normally, relationships are established using foreign keys.

When you have established all the relationships for these three tables, close the relationship screen and save the relationship layout when prompted.

You should print copies of the contents of each of your tables.

To print the contents of a table, list all the current tables in your database by clicking on the Tables object tab. Right click on the table you want to print. Then click on the Print command. It's just that easy!!

In later lab exercises we will learn about the other objects used to view data in a database; queries and forms.

Table 1: Data Types

Table 2: Commonly Used Data Fields

Table 3: Codes for the Input Mask

Table 4: Commonly User Validations

Table 5: ISBN Master List

Table 6: Inventory

Build your own data for the Book Location table. Use Borrower ID 001, 002 and 003. Distribute the books at the Squirrel Hill, Monroeville and Oakland branch libraries. Allow a default of 21 days for Days-May-Keep. The books by John Grisham are so popular that they may only be kept 7 days. Make sure you enter at least 10 records in this table. (Theoretically this table should have one entry for each copy of each book listed in the Inventory file. That would make this exercise too long for our class time.)

Return to Home Page