LearnHub

(This lesson includes a link to the ContactsDB.mdb database, as it stands at the end of this lesson. The link is at the bottom of this lesson.)

Open Microsoft Access. Click on File, New, and the New File pane will open. Select Blank Database, call it ContactsDB.mdb and save it. Close the New File pane if you wish. This will give you more Window room. Maximize the Objects window. You should be in the Tables collection window. If not, select it.

There should be three "Create table" icons. One of them is "Create table by using wizard". While we are not going to use this wizard in this course, it can quickly create a table depending on your needs. Explore it when you have the time.

Double-click "Create table in Design View". (Or select it and press Enter.)

You will see an empty design window with columns for Field Name, Data Type, Description. Referring back to our field list from the Table Design Lesson, we can start entering field names, data types (which I will dicusss), Description, and a more properties as we need them.

Enter "FirstName" under Field Name in the first row of the table design view. Tab over to Data Type, drop down the list and select "Text" (should be the first in the list), tab over to Description and enter "First Name". The Description is optional.

Here are some comments on field name naming conventions. There are several styles of naming conventions and over the years I have used more than one style. Take "FirstName" for example. This style capitalizes the first letter of each "word", "First" and "Name". Another style uses underscores, as in "First_Name". This renders the name readable but it involes a bit more typing. Finally the most obvious but least desireable style is to use "First Name", that is, separate the "words" with spaces as you would if writing a sentence. It's least desireable because wheen it comes time to write queries, you have to do more work. "SELECT First Name FROM Contacts" will not work because Access thinks you want two fields, not one, and expects a comma to separate the two field names, as in "SELECT First, Name FROM Contacts". To write this query properly you would have to say "SELECT [First Name] FROM Contacts". While the use of square brackets is the "safest" way to list fields in a SELECT clause, it's inconvenient if it's unnecessary. I find the the first style "FirstName" is most convenient and readable. And by "readable" I mean readable to you the developer, not the user. How the user sees this field name is accomplished by the use of Field name captions, which we have not addressed yet, and query aliases which we shall discuss when the time comes.

Returning to entering the first field name FirstName, look down at the bottom of the table design window where you will see two Field Properties tabs called General and Lookup. The Field Size is 50 characters which is a default value. You can change this default if you wish in the Tools menu, Options, Tables/Queries tab, Default field sizes, Text: property. Actually in many Access databases I have seen 255 (the maximum for a Text data type). Perhaps you see 255 as well, and not 50. Bear in mind that a field size of 255 chararcters is not a bad thing, It's not as though Access is reserving 255 characters for each row and that if you enter "Robert" as a first name you are wasting 255 - 6 = 249 characters. Instead think of it as Access allowing you to use up to 255 characters for a first name.

One thing to consider here is the actual length of the field. The length can act as a validation tool. For example, you may want to set the length to be quite short, say 10 characters, for a particular reason. Then your users will not be able to enter an 11 character first name. This has more practical applications in the business environment where for example, a part number should not be longer than 15 characters. The length of the First Name may be determined by cultural issues. Most English sounding names are probably shorter than 20 characters. Nowadays though in an increasingly global environment, we are exposed to many nationalities. Therefore a longer field size of say, 30 characters is better.

Other Field Properties in the General tab worth considering are Caption, Required, and Allow Zero Length. For a field such as FirstName, enter "First Name" for Caption, Yes for Required, and No for Allow Zero Length (which is consistent with the Required property). Now the user will see "First Name" in forms and reports. We set these properties this way because after all, this is a Contacts database and what good is a contact record if you do not know the person's first name? And in fact if you do not know the first name, you can always enter "Unknown".

This is what the table design view looks like so far in Figure 1 below.


Figure 1

(If you have trouble viewing this image on the page, here is the link to the image:
http://www.useexcel.com/access/tabledesign.jpg)

Here is where we can change the table design as we go along. Since this is a Contacts table, why not add a nickname? Nicknames are important to those who have them. Not knowing a person's nickname can make you sound formal if you address the person by the first name. Call it NickName, Text data type, description Nick Name, field size 50, caption Nick Name, required No, allow zero length Yes, since not everyone has a nick name.

You might consider a middle name or middle initial. If you want to add it, use the same properties as the NickName. And what about those who have four or more names, i.e. two or more middle names? I would enter them in the MiddleName field. In this case you might want to double the field size. Entering more than one middle name in one MiddleName field does break the First Normal Form rule, but the consequences are minimal. We will leave it out in this database.

Enter the LastName field name and give the same properties as the FirstName.

You should consider a Salutation field name, especially for business purposes. We will add it to our Contacts table. It makes sense to have it as our first field name, but we already have FirstName as our first field name, so how do we do this?

Select or highlight the first row in the design view. Then select the Insert menu, Rows. Add Salutation and give it the same properties as NickName. In other words, it's optional. However, if we do enter a Salutation, we are going to do it in a way that makes it easy for the user and in a way that maintains consistency. We'll show how to do this later.

Enter the Address1 field name, data type Text, description Address Line 1, field size 255 (to be safe), caption Address Line 1, required No, allow zero length Yes. Do the same for Address2.

Enter the City field name, data type Text, description City, field size 255, caption City, required No, allow zero length Yes.

Enter the State field name, data type Text, description City, field size 2, caption State, required No, allow zero length Yes. This applies to American states and Canadian provinces which are mutually exclusive. For other countries you may have to make changes. Since there are more than 50 American states and territories and 11 Canadian provinces, we will create a table of states and provinces later. For any other country I would do the same thing.

What about a field for Country? Considering the global nature of even our personal lives, I would add it. It has a Text data type, description Country, field size 50, required No, allow for zero length Yes. Like the State field, we will have a table for Country names.

In the Table Design course I said we were going to use Home, Work, Mobile, and Fax phone numbers. So add a HomePhone field name, Text data type, description Home Phone, field size 50, caption Home Phone. There is one more property to consider and that is Input Mask. This property faciliates data input. Access has built in input masks and an American/Canadian phone number input mask is one of them. The example Access shows is (206) 555-1212. So the user upon entering a phone number would only have to type the 206 555 1212 part. It is convenient but your users must be told about the mask and they have to get used to it. In a heavy data entry application it is very convenient.

If you click on the elipses (...) on the righ hand side of the Input Mask property row, the Input Mask Wizard will pop up. Select Phone Number, Next, Next. In the second last step of the wizard you have the choice of storing the phone number  as (206) 555-1212 or as 2065551212. While I am more of a purest and would choose the latter, let's go with the former. Choosing the 2065551212 option requires more work when it comes to reporting the value.

Enter WorkPhone with the same specifications.

Also add a WorkExt field, data type Text (not Number, since an extension of 0123 will be stored as 123), description Extension or "ext.", field size 10, caption "ext."

Enter FaxNumber and MobilePhone with the same specifications as WorkPhone.

We are near the end.

Since this is a Contacts table, why not add some personal information?

Add a Birthday field name, Date/Time data type, description Birthday, caption Birthday. We'll use a new property for Birthday, and that is the Format property in the Filed Properties Geneeral tab. Drop the list down and select Short Date so dates will look like 6/19/1994. This is an American date format in the more general format of mm/dd/yyyy.

Let's add a Comments field name. Perhaps you have some of these people over for dinner and they have food allergies. Here is where you can take note of this. Field name Comments, Text data type, description Comments, field size 255, required No, allow for zero length Yes. If you think you want a larger field size, change the data type to Memo. A Memo data type can hold up to 65,535 characters.

We have just one more field to add but first, in order to understand why we need this last field, save the table. The Save As dialog box will pop up. Change the Table Name to Contacts, click the OK button. Another dialog box will pop up telling you that "There is no primary key defined. Do you want to create a primary key now?" Click the Cancel button.

A primary key field uniquely describes each row in a table. Let's look at some possible candiates for primary key in the Contacts table.

A combination of FirstName and LastName. This combination fails as a primary key as soon as you try to enter a second "Robert Smith" record. And since it is possible to have two "Robert Smith"s living on the same street with the same birthday, it's clear that no combination of existing field will uniquely describe each record.

This is where a special numeric data type will help us - the AutonNmber data type.

Select or highlight the first field name "Salutation" and select the Insert menu, Rows. Enter ContactID as the field name, data type AutoNumber, description ContcatID. The AutoNumber data type will start off with a value of 1 for record 1, 2 for record 2, and so on. If you ever delete record 2, ContactID 2 will not be reused. Look down at the Indexed property in the General tab of the Field Properties. It should say "Yes (Duplicates OK)". Allowing duplicates will prevent the ContactID field from being a primary key.

To make ContactID truly the primary key, look for the gold key icon in the Table Design toolbar, which should be visible since we are in table design view. Select the ContcatID field name and click the gold Primay Key icon. You will see the gold key appear to the left of the ContactID field name. Look down at the Indexed property in the General tab of the Field Properties again. It should say "Yes (No Duplicates)".
Now save the table as "Contacts". Close the desgin view and return to the Object view for Tables.

You can download the database from here to see what it looks like so far http://www.useexcel.com/access/ContactsDB.mdb