(This is section 2 of 7 in this chapter)
After you have created your database, you need to create at least one table. Access uses tables to define the structure of the data in your database. Each table contains a collection of related information. Access makes it easy to construct tables. Access also provides a Table Wizard to help you create common types of tables. You will use the Table Wizard to create your first database table.
Note: Make sure you don't confuse a table with a database. In some software, a database is simply a list of information, much like a table in Access. However, a database in Access is much more than a table. For more information, refer to Chapter 1.
Before you start the Table Wizard, make sure that the Tables button is selected in the Database window (your screen will appear as shown earlier in Figure 2-3). Then, double-click your mouse on the Create Table By Using Wizard option. Momentarily, you should see the Table Wizard dialog box shown in Figure 2-4. In this dialog box, Access asks you to select the type of table you want to create.
Before you select a table type, take a minute to look at the Table Wizard dialog box. This dialog box is similar to other Wizard screens: at the top is some help information, in the center is where you make your choices, and at the bottom are the Wizard control buttons. You use these buttons to move from one step in the task to the next. The purpose of these buttons is shown in Table 2-1.
|Cancel||Stops the Wizard; no action is taken.|
|Back||Returns to the previous step (if available).|
|Next||Proceeds to the next step (if there is one).|
|Finish||Ends the Wizard; completes the action with the available information.|
Notice that at this point only one of the control buttons is available; the others are dim. Access makes only those control buttons available which have meaning at the current time. Thus, the Back button is not available because there is no previous step; you are at the first step right now. The Next and Finish buttons are not available because you need to make some choices before the Wizard can progress.
At this particular step of the Table Wizard, Access asks you to specify the type of table you want to create. Notice that there are two choices--Business and Personal--in the center of the left side of the dialog box. The button you select affects the types of tables listed in the Sample Tables list. Just for practice, select the Business button. Notice that there are many different sample tables listed. You can view all of them using the scroll bar at the right side of the Sample Tables list. Table 2-2 describes the different sample business tables which the Table Wizard can create.
|Mailing List||Creates a simple or complex mailing list.|
|Contacts||Tracks your sales contacts.|
|Customers||Manages your business customers.|
|Employees||Maintains information about your company's employees; can work with the Expenses and Time Billed sample tables.|
|Products||Develops your own inventory database.|
|Orders||Tracks orders for your products.|
|Order Details||Tracks order details (individual line items); works in relation to the Orders sample table.|
|Suppliers||Tracks your vendors.|
|Categories||Builds a category list which you can use in conjunction with other tables.|
|Payments||Records payments made by your customers.|
|Invoices||Maintains information about invoices generated.|
|Invoice Details||Tracks invoice details (individual line items); works in relation to the Invoices sample table.|
|Projects||Tracks the projects on which you or your employees are working; works in relation to the Time Billed sample file.|
|Events||Develops lists of special events; works in conjunction with the Reservations sample table.|
|Reservations||Manages reservations for a particular event; works in conjunction with the Events sample table.|
|Time Billed||Tracks time expended on projects; works with the Projects, Employees, and Expenses sample tables.|
|Expenses||Maintains a history of expenses related to specific projects; works with the Projects and Employees sample tables.|
|Deliveries||Tracks deliveries of your product; can be related to the Customers, Orders, and Employees sample tables.|
|Fixed Assets||Develops a list of the fixed assets owned by your company.|
|Service Records||Logs when a service is performed on an asset; can work with the Fixed Assets and Employees sample tables.|
|Transactions||Maintains a record of business transactions performed; can work with the Payments sample table.|
|Tasks||Tracks a list of tasks to be accomplished.|
|Employees And Tasks||Builds relationships between the Employees and Tasks sample tables.|
|Students||Maintains information about students (or employees) and their classes.|
|Students And Classes||Builds relationships between the Students table and a sample Classes table.|
Note: Remember that the sample tables used in the Table Wizard are just that--samples. However, you can modify them slightly while you are using the Table Wizard. Also, you can later change the design of any table created with the Table Wizard.
Since you want to create a database of your personal friends, you will need to select the Personal category. To select this group of sample tables, click your mouse on the Personal option button. As shown in Figure 2-5, Access changes the list of sample tables.
Scroll through the sample tables. You will see that the Table Wizard can create many different personal tables. For a list of these tables, refer to Table 2-3.
|Addresses||Creates a mailing list of your friends and acquaintances.|
|Guests||Tracks guests invited to a social event.|
|Categories||Builds a category list which you can use in conjunction with other tables.|
|Household Inventory||Maintains a record of your belongings for insurance purposes.|
|Recipes||Manages your favorite quiche, fondue, and other recipes.|
|Plants||Tracks the contents of your greenhouse or backyard jungle.|
|Exercise Log||Records exercises you have done and calories burned.|
|Diet Log||Manages your diet on a meal-by-meal basis.|
|Wine List||Tracks your favorite wines and vineyards.|
|Rolls of Film||Logs the rolls of film you shoot; you can use it with the Photographs sample table.|
|Photographs||Maintains a log of your photographs; you can use it with the Rolls of Film sample table.|
|Authors||Lists your favorite authors; you can use it with the Book Collection sample table.|
|Books||Tracks books in your personal library; you can use it with the Authors sample table.|
|Groups||Lists group photographs and any notes you have about them.|
|Service Records||Logs when a service is performed on an asset; with some modification, this table can work with the Household Inventory sample table.|
|Recording Artists||Lists your favorite artists; you can use it with the Recordings sample table.|
|Recordings||Tracks your recorded music; you can use it with the Artists sample table.|
|Video Collection||Organizes your video tapes and laser discs.|
|Accounts||Manages your investment accounts; works with the Investments sample table.|
|Investments||Tracks your individual investments; works with the Accounts sample table.|
Take a moment to select different sample tables. As you use the mouse to highlight different sample table names, notice that Access changes the information in the Sample Fields list.
To create a table to keep track of the names, addresses, and phone numbers of your friends, highlight Addresses in the Sample Tables list. (There is a good chance that this option is already highlighted; it is the first option in the list.) You are now ready to select the fields that will appear in your table. Your screen should appear as shown earlier, in Figure 2-5.
Within your table, you will specify the fields, or pieces of information, you want to track. The middle column, labeled Sample Fields, is where you specify the fields you want to use in your table. Remember from Chapter 1 that fields define the type of information Access maintains for each record in your table. Thus, at this point, you are defining the type of information you want to keep on each of your friends and acquaintances. Table 2-4 shows the fields from which you can make your selection.
|AddressID||A unique numeric ID for your friend's address|
|FirstName||Your friend's first name|
|LastName||Your friend's last name|
|SpouseName||The name of your friend's spouse|
|ChildrenNames||The name of your friend's children|
|Address||Your friend's street address|
|City||Your friend's city|
|StateOrProvince||Your friend's state or province|
|PostalCode||Your friend's ZIP code|
|Country||Your friend's country|
|EmailAddress||Your friend's electronic (Internet, Prodigy, and so on) mail address|
|HomePhone||Your friend's home phone number|
|WorkPhone||Your friend's work phone number|
|WorkExtension||Your friend's extension at work|
|MobilePhone||Your friend's mobile (cellular) phone number|
|FaxNumber||Your friend's fax line phone number|
|Birthdate||Your friend's date of birth|
|SendCard||An indicator of whether you want to send a birthday card to this friend|
|DateLastTalkedTo||Date you last had contact with your friend|
|DateUpdated||Date of last change to the database record|
|Nickname||Any nicknames or preferred names for your friend|
|Notes||Any comments or notes you may want to include|
|Hobbies||Any of your friend's hobbies or interests|
|HealthIssues||Any of your friend's health problems|
|Photograph||A photo of your friend|
The Table Wizard provides 25 sample fields for the Addresses sample table. However, you don't need to use all of them. Instead, you should pick the fields you think you want to maintain. Remember, you will need to keep the information up-to-date for it to be of value. The more fields you select, the more fields you must maintain.
To select a field, highlight the field name and then click your mouse on the button with the greater than symbol (>). Access, in turn, will move your selected field to the third column in the dialog box, which is labeled Fields In My New Table. Since you will probably want to include at least the first names, last names, addresses, and phone numbers of your friends, highlight each of these fields in the Sample Fields list. Then, after each is highlighted, click your mouse on the > button. When you are done, the dialog box should appear as shown in Figure 2-6.
If you make a mistake, don't despair. You can use the dialog box buttons between the second and third columns to manipulate each of the fields you will include. Table 2-5 lists these buttons and their purposes.
|>||Includes a field from the Sample Fields column in your table.|
|>>||Includes all fields from the Sample Fields column in your table.|
|<||Removes a field from your table.|
|<<||Removes all fields from your table.|
Note: You can move fields around in your table by removing them and then adding them again. Access adds a selected field to your table immediately after whatever field is highlighted in the third column.
Include whatever fields you deem necessary for your table. Make any modifications you desire until you have completely defined the field order you desire. Before moving on, make sure that you add the AddressID field to your table. This field assigns a primary key to your table. In the following two sections, you will learn the importance of adding the AddressID field. When you are done, click your mouse on the Next button to tell Access you are through with this step and ready to move on. Access will display the next step dialog box shown in Figure 2-7.
You are nearly finished defining your table. However, Access needs to know a few more pieces of data before it can proceed. At the top of this dialog box, Access asks you to name your table. Don't confuse this name with the name you gave your database when you first created it. Here you are naming your table, and the database you already created can contain many such tables. For this example, you will accept the default name (Addresses).
In this dialog box, you also specify whether you want Access to use a primary key for your table. A primary key is a field that Access uses to sort your table. Generally, a primary key should be unique, meaning that there will only be one record with any given primary key; Access will not let you add a record if it has the same primary key as another record.
For example, if you have three friends with the last name Smith, you cannot use the last name as a primary key. Likewise, if all your friends live in Seattle, you cannot use the city as a primary key. However, since your friends probably have unique phone numbers, you could use the phone field as the primary key.
You can set the primary key one of two ways. You can let Access set a key for you (this is the default), or you can specify the key yourself. Make your choice and then click your mouse on the Next button.
A primary key defines how Access orders information in your table. Even though you don't need to define a primary key, Access will function more efficiently if you do. Using a primary key, Access can locate and display records very quickly. This improved performance will be more important as you add more records to your database.
Access lets you define both unique keys and non-unique keys. Using a unique key is best because it improves searching time. However, there may be situations in which a unique key is not possible. For example, you may want to use a city name as the key. Since there are many cities with the same name, you would want to specify this as a non-unique key.
In addition, you can use multiple fields to define your key. However, you cannot use the Table Wizard to do so. Instead, you must create the table manually. You will learn how to do this in Chapter 4, "Creating Another Database."
When you choose a field to use as a primary key, you should select a field that is as close to unique as possible, regardless of whether you allow duplicates or not. For instance, you would not want to use the FirstName or LastName field as the primary key; there can be way too much duplication in these fields. On the other hand, you probably could use the HomePhone field since there is much less chance for duplication in this field.
If you let Access define your primary key automatically (at the end of the previous section), you can skip this section entirely and proceed to the next one. If you set the key yourself, you will see the dialog box shown in Figure 2-8.
Using this dialog box, you can select which field Access will use as the key. Access suggests a field to use (the first one in your table layout), but you can select a different one by clicking your mouse on the drop-down arrow at the right side of the field. For this example, make sure the AddressID field is selected.
Note: If you forgot to include the AddressID field in your table layout, you can use the Back button to return to the dialog box where you define fields. (You will need to click your mouse on the button twice.) Add the field and then use the Next button to return to this dialog box.
Next, the dialog box asks you to specify how you want Access to treat the primary key. You have three choices:
The primary key option you select depends on how you plan to use the primary key. If you only want a counter (a key whose value corresponds to the record number: 1, 2, 3, and so on), select the first option. However, if your primary key will be something else, choose one of the other options. When you are done, click your mouse on the Next button.
To finish your table, you must now answer two more questions. Both questions regard what you want to do when the Table Wizard is done. At this point, you should see the dialog box shown in Figure 2-9.
In this dialog box, you specify what you want to do next. Your choices are as follows:
In Chapter 9, "Creating Custom Forms," you will learn how to work with forms. Right now, choose the second option (which is the default). If you feel you need step-by-step hand-holding while using Access, you can choose the Display Help check box at the bottom of the dialog box.
As soon as you click your mouse on the Finish button (notice the Next button is no longer available; there are no further steps), the Table Wizard creates the table according to your specifications. You will know the Wizard is done when you see the Datasheet view of your table on the screen.
Note: Congratulations on designing your first database and table. In other database systems, what you have just accomplished could take hours. Your table is now ready for you to enter information, and you are fast on your way to becoming proficient with Access.