Enable data integrity access lookup wizard

Access Assignment

USING MICROSOFT ACCESS 2016 Guided Project 2-1

Guided Project 2-1 Central Sierra Insurance wants to expand the database you created in the Pause & Practice 1-4 project in Chapter 1. To ensure consistency, the starting file is provided for you. For this project, you use Design view to create a second table, edit field properties, and integrate data integrity rules. You also create a relationship between the two tables and enforce referential integrity constraints. Finally, you view how the table records will print using Print Preview. This project has been modified for use in SIMnet®.

Skills Covered in This Project • Create a table using Design view. • Add fields into a table. • Edit field properties in Design view. • Define an input mask. • Use the Lookup Wizard to create a drop-

down list.

• Save a table.

• Enter records. • Verify the existence of a foreign key. • Create a 1:M relationship between tables. • Enforce referential integrity. • View a table in Datasheet view and expand

related records.

• Preview table results for printing.

1. Open the CentralSierra-02 database start file.

2. The file will be renamed automatically to include your name. Change the project file name if

directed to do so by your instructor.

3. Enable content in the security warning.

4. Click the Table Design button [Create tab, Tables group] to open a new table in Design view.

5. Add new fields into the table and set field properties.

a. Type Branch in the Field Name property box of the first row. Press the Tab key to move to the

Data Type property.

b. Select the Short Text Data Type. Press the Tab key to move to the Description property.

c. Type Unique branch name.

d. Click the Primary Key button [Table Tools Design tab, Tools group].

e. Press the Tab key to move to the next row.

f. Add the remaining fields into the table using the following information:

g.

h.

i.

j.

k.

l.

m. Click the Save button to save the table as Branches. It is a good idea to periodically save a

table when you are creating it so you don’t lose your work.

n. Click the row selector to select the Branch field.

o. Type 30 in the Field Size property box in the Field Properties area.

Step 1: Download start file

Access 2016 Chapter 2 Using Design View, Data Validation, and Relationships Last Updated: 10/9/18 Page 2

USING MICROSOFT ACCESS 2016 Guided Project 2-1

p. Set the Required property to Yes if necessary.

q. Click the row selector to select the Address field.

r. Change the remaining field properties in the Branches table using the following information:

s.

t.

u.

v.

w.

x.

y. Save the table by clicking the Save button.

6. Create an input mask for the ZIP field.

a. Click to select the ZIP field.

b. Click the property box for the Input Mask property.

c. Click the Build button to launch the Input Mask Wizard.

d. Select the Zip Code Input Mask.

e. Click the Next button.

f. Click the Next button without making any changes to the Input Mask or Placeholder

character.

g. Select the Without the symbols in the mask, like this radio button if necessary.

h. Click the Finish button.

i. Save the table.

7. Create an input mask for the Phone field.

a. Click to select the Phone field.

b. Click the property box for the Input Mask property.

c. Click the Build button to launch the Input Mask Wizard.

d. Select the Phone Number Input Mask.

e. Click the Next button.

f. Click the Next button without making any changes to the Input Mask or Placeholder

character.

g. Select the Without the symbols in the mask, like this radio button if necessary.

h. Click the Finish button.

i. Save the table.

8. Enter records into the Branches table.

a. Change to Datasheet view of the table.

b. Enter the following records into the Branches table. The DateOpened values will display in

the Medium Date format, DD-Mon-YY, after you enter them.

c.

d.

e.

Access 2016 Chapter 2 Using Design View, Data Validation, and Relationships Last Updated: 10/9/18 Page 3

USING MICROSOFT ACCESS 2016 Guided Project 2-1

f. Save and close the table.

9. Modify the Department field in the Employees table to use a Combo Box.

a. Open the Employees table in Design view.

b. Click to select the Department field.

c. Click the property box for the Data Type property, click the arrow, and select Lookup Wizard.

The Lookup Wizard dialog box opens.

d. Select the I will type in the values that I want radio button.

e. Click the Next button.

f. Click the first cell in the Col1 column.

g. Type Administration.

h. Press the down arrow key to move to the next row.

i. Type Health and Benefits. Press the down arrow key to move to the next row.

j. Type Insurance Sales. Press the down arrow key to move to the next row.

k. Type Property and Casualty.

l. Click the Next button.

m. Click the Limit To List check box.

n. Click the Finish button.

o. Save the table. If prompted to test the existing data against the new rules, click Yes.

10. Verify the existence of a foreign key and create a one-to-many relationship between the

Branches and Employees tables. The Branch field is the field in common across the two tables. It

is the primary key in the Branches table.

a. Open both tables in Design view and verify that the Branch field has the same Data Type

and Field Size in both tables.

b. Edit the Description property of the Branch field in the Employees table to read Branch

assigned to – Must match a Branch in the Branches table.

c. Save and close any tables that you may have opened.

d. Click the Relationships button [Database Tools tab, Relationships group].

e. Select the Branches table In the Show Table dialog box and click the Add button.

f. Select the Employees table and click the Add button.

g. Click the Close button.

h. Drag the Branch field from the Branches table on top of the Branch field in the Employees

table. Release the pointer. The Edit Relationships dialog box opens.

i. Verify that the field names displayed are correct. If necessary, use the drop-down arrows to

make any corrections.

j. Click the Enforce Referential Integrity check box.

k. Click the Cascade Update Related Fields check box.

l. Leave the Cascade Delete Related Records check box not selected. The company does

not want to delete employees just because they may close a branch. This allows them to

reassign employees.

m. Click Create. The Relationships window updates with the relationship line added. The 1 and

infinity symbols display indicating that referential integrity is enforced.

n. Save the changes made to the relationships.

o. Click Close [Relationship Tools Design tab, Relationships group].

Access 2016 Chapter 2 Using Design View, Data Validation, and Relationships Last Updated: 10/9/18 Page 4

USING MICROSOFT ACCESS 2016 Guided Project 2-1

11. Enter the following record into the Employees table to test the new drop-down list:

12. Save and close the table.

13. Open the Branches table in Datasheet view.

a. Adjust the width of the fields so that the entire field contents shows for each field.

b. Click the plus sign to the left of the Branch field for the Cameron Park branch. You should

see the 11 employees assigned to Cameron Park.

c. Click the minus sign to close the related records.

14. Preview the data records in the Branches table before printing.

a. Click the Print button [File tab] to display the printing options.

b. Click the Print Preview button. The Print Preview tab opens and shows a preview of how the

table will print.

c. Click the Landscape button [Print Preview tab, Page Layout group] to change the page

orientation. The report preview should appear similar to that in Figure 2-82.

d. Click the Close Print Preview button.

15. Save and close the Branches table.

16. Close the database.

17. Upload and save your project file.

18. Submit project for grading.

Step 2 Upload & Save

Step 3 Grade my Project