Welcome to the second part in this series of articles discussing the basics of Microsoft’s Access 2000. In the first article, I walked you through the process of creating a table for a simple address book. This time around, we will look at creating some additional tables and forming relationships between them. If you would like to follow along with the examples, download our sample database (a simple address book) or create your own as we go.
Creating a phone number table
You’ll probably want each person’s phone number listed in your address book. In my sample database, I have included home phone, work phone, cell phone, and pager number (See Figure A).
|A simple table listing phone numbers|
Follow the same steps we used to create the Address table in the first part of this series to create your Phone table.
Be sure to include all the different phone numbers a person might have (as well as the person’s first and last name). It’s a good idea to restrict the field length of the phone number fields to eight (or 12 if you are using area codes). Reducing the length of these fields reduces the overall size of the database.
Adding a birthday table
Another bit of useful information to have on hand is someone’s birthday. All this table needs is the person’s first name, last name, and his or her birthday (see Figure B). I will be using this table again in the next part of this series to create forms.
|Now you won’t forget anyone’s birthday.|
Before you start entering data into the tables, you need to set up relationships between them. This will come in handy later when you create your forms.
To form a relationship between two tables, click on the Relationships button shown in Figure C.
|The Relationships button|
A window will appear that is used to select which tables you want to add to the relationship (see Figure D). Select each table and then click Add. When all tables have been added, click Close.
|Be sure to add all tables.|
You will notice that both the first name and last name fields in all of the tables are in a bold font, indicating that they are the primary keys.
Select both the first and last name fields in the Address table. (Click on the first name field and shift-click on the last name field.) Now that both fields are highlighted, drag and drop those onto the first name field in the Phone table (you can rearrange the tables on the layout screen by clicking the title portion of the table and dragging it around the screen). Another window, shown in Figure E, will appear asking you to match up the fields in the two tables.
|This window allows you to set properties for each relationship.|
The first name and the last name fields should already be listed under the Address table (this will serve as the main table for this database). Match up the first name and the last name fields under the Phone table (the related table, in my sample).
You will also notice a check box that states Enforce Referential Integrity. To someone with little to no experience with databases, this phrase can be fairly confusing. To enforce referential integrity means to create a rule that forces the data entered into the related table to appear in the main table. For example, say you have already entered the data for your Address table before you enter the data for your Phone table (like you should have done if you have followed my examples). When entering the data into the Phone table, if referential integrity is enforced, you will not be allowed to add a person’s name and phone numbers if that person’s name is not in the Address table. Basically, if the person does not exist in the Address table, they cannot exist in the Phone table.
If you want to set up your database so that you are allowed to enter a person’s name and phone numbers into the Phone table without their name appearing in the Address table, then do not enforce referential integrity.
There are some other choices to be made if you do indeed decide to enforce referential integrity.
Update related fields
To Cascade Update Related Fields means that data updated in the main table will automatically be updated in the related table. Using our example again, if you change someone’s name in the Address table, his or her name is automatically changed in the Phone table.
With Cascade Delete Related Records turned on, Access will delete records in the related table that are deleted from the main table.
Follow the same procedure with the Birthday table. Use the Address as the main table and the Birthday table as the related table. The layout might look a little confusing, but you can move the table boxes around the screen to at least make it more pleasing to the eye (see Figure F).
|Each relationship is shown as a line between the tables.|
Until next time…
That's all for this lesson. Now that the relationships are formed to your standards, you can begin to enter the data into the Phone and Birthday tables. Once that is done, we will be ready to move on to the final part in this series—creating forms.
Do you have a great Microsoft Access tip that would benefit your fellow TechRepublic members? What do you think of Jason's brief introduction to Access 2000? Post a comment or write to Jason and let him know.