MIS Project DUE in 6h

Access 2013 Chapter 2 Using Design View, Data Validation, and Relationships Last Updated: 2/27/15 Page 1

USING MICROSOFT ACCESS 2013 Independent Project 2-5

Independent Project 2-5 Life’s Animal Shelter wants to expand the database created in the Independent Project 1-5 in Chapter 1. To ensure consistency, the starting file is provided for you. In this project, you use Design view to create a second table, edit field properties, integrate data integrity rules, and enter data. You also create relationship between the two tables and enforce referential integrity constraints. Finally, you add data using the relationship between tables.

Skills Covered in This Project

x Create a table using Design view. x Add fields into a table. x Edit field properties in Design view. x Enter records. x Create a foreign key. x Create a 1:M relationship between tables.

x Create an input mask. x Create a validation rule and validation text. x Save a table. x Enforce referential integrity. x View a table in Datasheet view and expand

related records.

1. Open the LifesAnimalShelter-02.accdb database file.

2. Enable content in the security warning.

3. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.

4. Create a new table using Table Design. a. Add the following fields into the table and set field properties.

j. Set OwnerID as the primary key. k. Save the table as Owner.

5. Create an input mask for the Phone field. a. Select the Phone field. b. Use the Phone Number Input Mask. c. Don’t make any changes to the mask or placeholder character and select the radio button for

Without the symbols in the mask, like this. d. Finish the Input Mask Wizard. e. Save the table.

Step 1 Download start file

b.

c.

d.

e.

f.

g.

h.

i.

Access 2013 Chapter 2 Using Design View, Data Validation, and Relationships Last Updated: 2/27/15 Page 2

USING MICROSOFT ACCESS 2013 Independent Project 2-5

6. Create and test a field validation rule. a. Select the OwnerID field. b. Type Like "####" as a validation rule. c. Type You must enter a 4-digit Owner ID as

validation text. d. Save the table and switch to Datasheet view. e. Test the rule by typing 11 in the first row of the

OwnerID column. f. Move to the OwnerLastName field. The

message box should display the validation text message ( Figure 2-93 ) since the OwnerID is not four digits long.

g. Delete the contents and type 1111 into the OwnerID field. h. Verify that you can successfully move to the OwnerLastName field since 1111 meets the validation

rule criteria. i. Delete this test data record.

7. Enter the following records into the Owner table. Life’s Animal Shelter doesn’t have any data about the owners stored electronically so you are unable to import any data.

8. Save and close the table.

9. Determine the foreign key field. a. Currently there is not a field in common across the Owner and Pets tables. There is a one-to-many

relationship between the two tables since an owner could adopt many pets, but each pet is adopted by only one owner.

b. The foreign key comes by taking the primary key field from the 1 table and adding it in as an additional field in the M table.

c. Open the Pets table in Design view. d. Add the foreign key field below the Details field using the following information.

e. Save and close the Pets table.

a.

b.

c.

d.

e.

Access 2013 Chapter 2 Using Design View, Data Validation, and Relationships Last Updated: 2/27/15 Page 3

USING MICROSOFT ACCESS 2013 Independent Project 2-5

10. Create a one-to-many relationship between the Owner and Pets tables.

a. Open the Relationships window and add the two tables. b. Enlarge the table objects, if needed, to see all of the fields. c. Drag the OwnerID field from the Owner table on top of the FKOwnerID field in the Pets table.

Release the pointer. The Edit Relationships dialog box displays. d. Make the choices to Enforce Referential Integrity and Cascade Update Related Fields. e. Leave the Cascade Delete Related Records check box not selected. The shelter does not want to

delete pet records when it deletes information about an owner. f. Create the relationship. g. Save the changes and close the Relationships window.

11. Enter the following data into the specified records in the Pets table to reflect which pets have been adopted. a. Open the Pets table in Datasheet view. b. Type 0100 in the FKOwnerID field for PetID 1. c. Type 0103 in the FKOwnerID field for PetID 3. d. Type 0100 in the FKOwnerID field for PetID 12. e. Type 0104 in the FKOwnerID field for PetID 14. f. Close the Pets table.

12. View the related data records in the Owner table. a. Open the Owner table in Datasheet view. b. Click the plus sign to the left of the OwnerID field for the Albert Smith, OwnerID 0100. You should

see the two pets adopted by Albert. c. Close the Owner table.

13. Close the database.

14. Upload and save your file.

15. Submit project for grading.

Step 2 Upload & Save

Step 3 Grade my Project