** This practice is available on below Microsoft Office 2010 Training website**
** It is downloaded and presented here for "convenience use of in-class practice" for my students**

http://office.microsoft.com/en-us/access-help/practice-RZ101772997.aspx?section=9

Create the tables for a database

The first course in this series, "Design the tables for a new database," gave you a list of tables and fields for use in the sample Assets database. In the list, (PK) indicates a primary key field, (FK) indicates a foreign key field, and (lookup) indicates a lookup field.

You can use this list during the practice or substitute your own design. If you think it will help, print these instructions and refer to them as you go.

Also, note that several fields are marked as lookup columns. During this practice, you'll create them for the Suppliers and Support tables, but Access will create the fields in the Assets table for you, and you'll change the values they contain.

Exercise 1: Create the Suppliers table in Datasheet view

You'll start by creating the Suppliers table in Datasheet view. Along the way, you’ll use some Quick Start fields.

When you started this practice session, the sample database opened without any tables or other objects. Ignore any security warnings.

Create a new, mostly blank table

1. If you see the yellow Security Warning, click Enable Content. That adds the database to your list of trusted files, and you never see another security message.
2. On the Ribbon, click the Create tab, and in the Tables group, click Table.

A new table opens in Datasheet view. The table automatically includes a primary key field called ID.

3. Double-click the ID column header, enter SupplierID, and press ENTER. That shifts the focus to the next blank field, and a menu of data types appears.
4. In the menu of data types, click Text. Access adds a field to the table, sets the field to the Text data type, and makes the header writable.
5. In the header, enter SupplierFirstName and press ENTER. The focus shifts again, and the menu of data types appears again.
6. Repeat steps 3 and 4 and create another text field called SupplierLastName.

Set captions

1. Click the SupplierFirstName field header.
2. On the Fields tab, in the Properties group, click Name & Caption. The Enter Field Properties dialog box appears.
3. In the Caption text box, enter Supplier First Name and click OK.
4. Click the SupplierLastName field header, repeat step 2, and enter the caption Supplier Last Name.

Add Quick Start fields

1. Click the header of the next blank field in the table.
2. On the Fields tab, in the Add & Delete group, click More Fields.
3. Scroll down the menu to the Quick Start section and click Address.

After a few moments, a set of address fields appear in the table.

4. Repeat steps 1- 3, but this time, under Quick Start , click Phone. A set of fields for capturing phone numbers appears.
5. Right-click the Home Phone field and click Delete Field.
6. Use what you've learned to add a Text field named Email and a Memo field named Comments.
7. When you finish, press CTRL+S, and in the Save As dialog box, enter Suppliers and click OK.

Note    You may have noticed that the Category field, listed in the table at the start of this practice, is missing from the table. That's okay. The Category field is a lookup field, and you'll create it later in this practice.

Exercise 2: Create the Support table in Design view

Design view isn't as fast a way to create tables as the methods you've seen, but it’s the most detailed and thorough. This exercise shows you how to start the designer, add fields, set data types, and define a primary key.

Important    Remember you can’t use Design view for web databases.

1. Go to the Ribbon, click the Create tab, and in the Tables group, click Table Design.
2. A new table opens in Design view.
3. If it isn't already selected, click the first row in the Field Name column and enter SupportID.
4. Click the first cell in the Data Type column (the cell next to SupportID), and select AutoNumber from the list.
5. Go back to the Field Name column, right-click the SupportID cell and click Primary Key. This sets the field as the primary key for the table. If you don't set the field now, Access prompts you to do so when you save the table.
6. Enter the remaining field names and data types for the table.

Hint    Access automatically selects the Text data type when you put the focus on a blank row in the Field Name or Data Type columns. Access uses the Text data type by default because it’s the most flexible. It accepts numbers and text.

Here are the remaining fields and data types.

7. Optionally, in the Field Properties pane, on the General tab, click the text box next to Caption and enter a caption for each field. Captions are always optional; use them to make your life easier.
8. When you finish, press CTRL+S, and in the Save As dialog box, enter Support and click OK.

Exercise 3: Create the Assets table

This one will take less time.

1. Click the Create tab.
2. Use what you’ve learned in exercises 1 and 2 to create the third table in the database and name it Assets. In other words, in the Tables group, click either Table or Table Design. Your choice.
3. Create a table with these fields and data types:

Note    Remember to set the AssetID field as a primary key.

4. Optionally, assign captions to each field as needed.

If you scroll to the top of these exercises and look at the fields listed there, you’ll see more fields for the Assets table than you see here – Location, Condition, Supplier, and Support. The Location and Condition fields are Lookup lists, and you’ll add them in exercise 5. The Supplier and Support fields are foreign keys. Leave those out for now, and we’ll add them in the next course in this series, when we create relationships.

Exercise 4: Create lookup fields in Datasheet view

You can create two types of lookup fields: one looks up values in another database table; the other uses a value list — a set of options stored in the properties for the field. These steps explain how to create value lists in the Suppliers and Support tables. For information about creating the other type of lookup field, take the next course in this series, or see the links in the Quick Reference Card at the end of this course.

1. Right-click the Support tab and click Datasheet View.
2. Scroll to the first blank field (the words Click to Add), click the header, and on the menu, click Lookup & Relationship. The first page of the Lookup Wizard appears.
3. Click I will type in the values that I want, and then click Next.
4. Make sure the Number of columns box contains a 1, and in the grid under Col 1 enter the following values:

• Computers
• Office Machines
• Furniture
• Gen. Maintenance

Your results should look like this:

Note    If the values in your list contain multiple words, you can separate those words with spaces. You should avoid using spaces in field names, but they're OK here.

5. Click Next, and in the What label would you like for your column box, enter Category and click Finish.

Do you like where Access placed the column in the table? If not, click the Category column header, then click it again and drag the header to a location that works better for you, perhaps to the right of the SupportCompany field.
 

6. Now take a deep breath, click the Suppliers tab, and repeat steps 1-5 to create a lookup field in that table. Name the field Category, and enter these options in the value list:

• Computers

• Office Machines

• Furniture

• Misc. Supplies

7. Open the list in the Category field and select an option. Notice that Access creates a new record (a new row) in the table. You can tell because the SupplierID field now contains a value.

Exercise 5: Create lookup fields in Design view

The Assets table needs two Lookup fields – Condition and Location. Both fields need to be value lists, and you can do the job in Design view.

Create the Condition field

1. If it isn’t already, open the Assets table in Design view.
2. Click the first blank row in the Field Name column and type Condition.
3. Click outside that new field. That sets the value in the Data Type column to Text. You need to set a data type before you can proceed, and Text works fine.
4. Click the Condition field again to select it, and at the bottom of the designer, under Field Properties, click the Lookup tab.

You see the words Display Control and Text Box.

5. Click Text Box and a list will appear. Open the list and select List Box, like so:

6. Go to the Row Source Type row and click Table/Query. That starts another list. Open that list and select Value List, like so:

For now, ignore the smart tag that appears

7. Click the Row Source field and enter the following values as shown (be sure to include the double quotes and semicolons):

"New";"Good";"Fair";"Retire"

You can enter them manually, or copy and paste the entire string. Press ENTER when you're done. After you press ENTER, a smart tag appears.

8. In the smart tag, click the option for updating the lookup field wherever it's used.

A message tells you Access didn't update the field anywhere else in the database. Access may not need to update shared instances of the lookup field, but you should let the program try. Click OK to close the message.

Create the Location field

1. Repeat steps 2 through 8 in the previous exercise, but this time create a field named Location.
2. Enter these values in the Row Source field:

"First Floor";"Second Floor";"Loading Dock"

3. Make sure you use the smart tag to update the lookup properties, and then press CTRL+S to save your changes.
4. Right-click the tab for the table, click Datasheet View, and then open the list in the Location and Conditions fields and select an option.

Access adds a new record (a new row) to the table.