Homework answers / question archive / Illustrated Access 2016 | Module 5: SAM Project 1b Salvage Database MODIFYING A DATABASE STRUCTURE GETTING STARTED •   Open the file IL_AC16_5b_FirstLastName_1

Illustrated Access 2016 | Module 5: SAM Project 1b Salvage Database MODIFYING A DATABASE STRUCTURE GETTING STARTED •   Open the file IL_AC16_5b_FirstLastName_1

MS Access

Share With

Illustrated Access 2016 | Module 5: SAM Project 1b
Salvage Database
MODIFYING A DATABASE STRUCTURE

GETTING STARTED
•   Open the file IL_AC16_5b_FirstLastName_1.accdb, available for download from the SAM website.
•   Save the file as IL_AC16_5b_FirstLastName_2.accdb by changing the “1” to a “2”.
o   If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
•   To complete this Project, you will also need to download and save the following support file from the SAM website:
o   Support_IL_AC16_5b_Application.docx
•   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 Volunteers table to it. Create a one-to-many relationship between the Organizations table and the Volunteers table using the OrganizationID field from both tables. Enforce referential integrity on this relationship, then save the Relationships window.
2.   Create a Relationships Report, save it with the name Relationships, then close it. Close the Relationships window.
3.   Open the Organizations table in Design View, then delete the OrgFirst and OrgLast fields.
4.   While still viewing the Organizations table in Design View, complete the following tasks:
a.   Move the OrgWebSite field between the OrgName and OrgStreet fields.
b.   Use the Lookup Wizard to change the OrgState field to a Lookup field. In the Lookup Wizard, type in the following values in the list in Col1: AL, GA, and FL. (Hint: Do not type the period.) Use OrgState as the label for the lookup field, and limit the entries to the list. Do not allow multiple values.
c.   Add a new field to the end of the list, after the OrgPhone field, named Application with the Attachment data type.
d.   Save the Organizations table, switch to Datasheet View, then use the OrgState Lookup field to change the value for OrganizationID 2 from FL to AL.
5.   In the new Attachment field at the end of the record for OrganizationID 1, attach the Support_IL_AC16_5b_Application.docx file, available for download from the SAM website, to the field. Close the Organizations table.
6.   Open the Volunteers table in Design View, then enter First Name as the caption for the VolFirstName field and Last Name as the caption for the VolLastName field.
7.   While still viewing the Volunteers table in Design View, enter Unique number for primary key as the description for the VolunteerID field. Save and close the Volunteers table.
8.   Open the Deposits table in Design View, then complete the following tasks:
a.   Apply the custom format mm/dd/yy to the DepositDate field.
b.   Change the Required property for the DepositDate field to Yes.
c.   Change the Field Size property of the Weight field to Single, and change the Decimal Places property to 0.
d.   Change the Default Value property of the Weight field to 1.
e.   Add a Validation Rule property to the Weight field that only allows values that are less than or equal to 500.
f.   Add a Validation Text property to the Weight field that reads Values must be 500 or less. (Hint: Do not include the period.)
Save the Deposits table, click Yes when prompted that some data may be lost, click Yes when prompted that data integrity rules have been changed, then close the Deposits table.
9.   Open the Volunteers table in Design View, then add a field below the VolJoinDate field and update it as described below:
a.   Set the field name to VolCellPhone. (Hint: Do not type the period.)
b.   Set the data type to Short Text, then save the Volunteers table.
c.   Use the Input Mask Wizard to apply a Phone Number Input Mask to the new VolCellPhone 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.
10.   Switch to viewing the Volunteers table in Datasheet View then complete the following tasks:
a.   Move the VolJoinDate field between the VolunteerID and OrganizationID fields.
b.   Enter (555)888-2233 as the VolCellPhone field value for VolunteerID 1 (Mahring), then enter (555)999-2244 as the VolCellPhone field value for VolunteerID 2 (Carpenter), as shown in Figure 1 on the next page.
Save and close the Volunteers table.

Figure 1: Volunteers Table in Datasheet View

Save and close any open objects in your database. Compact and repair your database, close it, then exit Access. Follow the directions on the SAM website to submit your completed project.