Illustrated Access 2013 Unit E: SAM Project 1a Volunteer Activities MODIFYING A DATABASE STRUCTURE PROJECT DESCRIPTION As a part-time emergency volunteer, you have developed a database to help track a network of volunteers and the time they donate to various emergency relief activities. In this project you will work to improve the tables and structure of this database. GETTING STARTED Download the following file from the SAM website: o IL_Access2013_UE_P1a_FirstLastName_1.accdb Open the file you just downloaded and save it with the name: o o IL_Access2013_UE_P1a_FirstLastName_2.accdb Hint: If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access will add the file extension for you automatically. Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Open the Relationships window and add the Zips table to it. Create a one to many relationship between the Zips table and the Volunteers table using the Zip field. Enforce referential integrity on this relationship then save and close the Relationships window. 2. Open the Volunteers table in Design View and Delete the Dues and CharterMember fields. 3. In Design View of the Volunteers table, move the Birthday field between the LastName and Street fields. 4. In Design View of the Volunteers table, use the Lookup Wizard to change the Rank field to a Lookup field. In the Lookup Wizard, type in the values that you want in the list: First, Second, Life, and Eagle. Use Rank as the label for the lookup field, and limit the entries to the list. 5. Save the Volunteers table, switch to Datasheet View, and use the Rank Lookup field to change the value for VolunteerNo 1 from First to Second. Close the Volunteers table. 6. Open the Activities table in Design View and enter Description as the Caption for the ActivityDescription field. Illustrated Access 2013 | Unit E: SAM Project 1a 7. In Design View of the Activities table, enter Per person activity fee as the Description for the ActivityFee field. 8. In Design View of the Activities table, set a custom Format for the ActivityDate field of mm/dd/yyyy. 9. In Design View of the Activities table, change the Required property for the ActivityDate field to Yes. 10. In Design View of the Activities table, set the Format property of the ActivityState field to always display the entry in all uppercase characters. Save the Activities table, click Yes when prompted that data integrity rules have been changed, and then close the Activities table. 11. In Design View of the VolunteerHours table, change the Field Size property of the Hours field to Single, and the Decimal Places property to 1. 12. In Design View of the VolunteerHours table, change the Default Value property of the Hours field from 0 to 1. 13. In Design View of the VolunteerHours table, add a Validation Rule property to the Hours field that allows values that greater than or equal to 1 and less than or equal to 12. 14. Add a Validation Text property to the Hours field that reads Values should be from 1 to 12 per day. Save the VolunteerHours table, click Yes when prompted that data integrity rules have been changed, and then close the VolunteerHours table. 15. In Design View of the Volunteers table, add a field below the Rank field and update as described below: a. Set the Field Name to CellPhone. b. Set the Data Type to Short Text. c. Use the Input Mask Wizard to apply a Phone Number Input Mask to the new CellPhone field that requires all 10 digits of the phone number, uses the * (asterisk) as the Placeholder character, and does not store the symbols in the mask. d. Save the Volunteers table. 16. Switch to Datasheet View for the Volunteers table and move the CellPhone field between the LastName and Birthday fields. Save the table. 17. While viewing the Volunteers table in Datasheet view, enter (555)111-3333 as the Phone value for VolunteerNo 1, Micah Downing as shown below in Figure 1. Illustrated Access 2013 | Unit E: SAM Project 1a Figure 1: Volunteers table Microsoft product screenshots used with permission from Microsoft Corporation. Copyright 2014 Cengage Learning. All Rights Reserved. Save and close any open objects in your database. Compact and repair your database, close it, and exit Access. Follow the directions on the SAM website to submit your completed project