Homework answers / question archive / Illustrated Access 2016 | Module 5: SAM Project 1a Dive Trips Database MODIFYING A DATABASE STRUCTURE   GETTING STARTED · Open the file IL_AC16_5a_FirstLastName_1

Illustrated Access 2016 | Module 5: SAM Project 1a Dive Trips Database MODIFYING A DATABASE STRUCTURE   GETTING STARTED · Open the file IL_AC16_5a_FirstLastName_1

MS Access

Share With

---

Illustrated Access 2016 | Module 5: SAM Project 1a

Dive Trips Database

MODIFYING A DATABASE STRUCTURE

 

GETTING STARTED

· Open the file IL_AC16_5a_FirstLastName_1.accdb, available for download from the SAM website.

· Save the file as IL_AC16_5a_FirstLastName_2.accdb by changing the “1” to a “2”.

· 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:

· Support_IL_AC16_5a_Scuba.jpg

· 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 Countries table to it. Create a one-to-many relationship between the Countries table and the DiveTrips table using the CountryName field from the Countries table and the Country field from the DiveTrips table. Enforce referential integrity on this relationship, then save the Relationships window.

1. Create a Relationships Report, save it with the name Relationships, then close it. Close the Relationships window.

1. Open the DiveTrips table in Design View and delete the AverageTemp and Climate fields.

1. While viewing the DiveTrips table in Design View, complete the following tasks:

3. Move the StartDate field between the Location and City fields.

3. Use the Lookup Wizard to change the Rating field to a Lookup field. In the Lookup Wizard, type in the values that you want in the list in Col1: 1, 2, 3, 4, and 5. Use Rating as the label for the lookup field, and limit the entries to the list.

3. Add a new field to the end of the list, after the TripReport field, named Photo with the Attachment data type.

3. Save the DiveTrips table, switch to Datasheet View, and use the Rating Lookup field to change the value for ID 1 from 4 to 5.

1. In the new Attachment field at the end of the first record for ID 1, attach the picture Support_IL_AC16_5a_Scuba.jpg, available for download from the SAM website, to the field. Close the DiveTrips table.

1. Open the Divemasters table in Design View and enter Last Name as the caption for the LName field.

1. While viewing the Divemasters table in Design View, enter Unique ID number as the description for the DiveMasterID field. Save and close the Divemasters table.

1. Open the DiveTrips table in Design View, then complete the following tasks:

7. Set a custom format for the StartDate field of mm/dd/yyyy.

7. Change the Required property for the StartDate field to Yes.

7. Set the Format property of the State field to always display the entry in all uppercase characters. Save the DiveTrips table and click Yes if prompted to confirm that data integrity rules have been changed.

7. Change the Field Size property of the Participants field to Single, and change the Decimal Places property to 0.

7. Change the Default Value property of the Participants field to 10.

7. Add a Validation Rule property to the Participants field that allows values that are less than or equal to 20.

7. Add a Validation Text property to the Participants field that reads Values must be 20 or less. (Hint: Do not include the period.)

7. Save the DiveTrips table, click Yes when prompted to confirm that data integrity rules have been changed, and then close the DiveTrips table.

1. Open the Divemasters table in Design View, then add a field below the PhoneNumber field and update it as described below:

8. Set the field name to AlternatePhone.

8. Set the data type to Short Text and save the Divemasters table.

8. Use the Input Mask Wizard to apply a Phone Number Input Mask to the new AlternatePhone 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.

8. Save the Divemasters table.

1. Switch to Datasheet View for the Divemasters table and complete the following tasks:

9. Move the PhoneNumber field between the Last Name and Address fields. Save the table.

9. Enter (555)111-2233 as the Phone Number value for DiveMasterID 1, Schwartz, and enter (555) 111-2244 as the AlternatePhone number for the same record as shown in Figure 1 on the next page.

 

Figure 1: Divemasters Table

 

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.