Microsoft assignments

Marquee Challenge Challenge 1 Modifying Tables and Creating a Table 1. Begin with 2-FCTTours-MC-1-HOA open and enable the content, if necessary. 2. Open the Tours table in Design view and then make the following changes: a. Move the Tour field between the TourID and BegDate fields. b. Delete the Discount field. c. Change the field size of the TourID field to 4 characters. d. This table contains tours only for the year 2022. Include a validation rule for the

BegDate field that limits entries to dates after (greater than) 12/31/2021. Include appropriate validation text that will display if an incorrect date is entered in the field. Include a validation rule for the EndDate field that limits entries to dates before (less than) 1/1/2023. Include appropriate validation text that will display if an incorrect date is entered in the field.

e. Enter the following records in the table. (Try entering incorrect dates in the BegDate and EndDate fields to determine if your validation rules work.)

TourID HS04 TourID BZ03 Tour Hawaiian Special Tour Brazil Highlights BegDate 4/3/2022 BegDate 4/24/2022 EndDate 4/9/2022 EndDate 5/4/2022 PriceSO $1,899 PriceSO $3,699 PriceDO $1,659 PriceDO $3,279

f. Adjust column widths to best fit the longest enties. g. Save and then close the Tours table. 3. Open the Agents table in Design view and then make the following changes: a. Delete the HireDate field. b. Change the field size of the AgentID field to 3 characters. c. Use the Lookup Wizard to specify the following choices for the Office field: Los

Angeles, San Francisco, and Toronto. d. Enter the following records in the table:

AgentID 107 AgentID 131 FirstName Jenna FirstName Rene LastName Williamson LastName Forbrege OfficePhone 213-555-0939 OfficePhone 905-555-4321 CellPhone 562-555-3495 CellPhone 647-555-4389 Office Los Angeles Office Toronto

4. Save and then close the Agents table. 5. Create a new Bookings table with the fields shown below. You determine the

data types and include appropriate captions for some or all of the fields. Change the field number size where appropriate and identify the BookingID field as the primary key field.

BookingID (specify the AutoNumber data type and set as the primary key field) BookingDate (date the booking was made by the agent) TourID (the tour identification number from the Tours table) AgentID (the agent identification number from the Agents table) NumberPersons (number of people booked on a specific tour)

Marquee Series © Paradigm Publishing, LLC Access 365, 2019 Edition: Section 2, Challenge 1 Hands On Activity

Type the following records in the table: BookingID (Access inserts number) BookingID (Access inserts number) BookingDate 6/12/2021 BookingDate 6/14/2021 TourID AF02 TourID HC01 AgentID 114 AgentID 109 NumberPersons 8 NumberPersons 2

BookingID (Access inserts number) BookingID (Access inserts number) BookingDate 6/16/2021 BookingDate 6/16/2021 TourID CR02 TourID AK01 AgentID 103 AgentID 137 NumberPersons 2 NumberPersons 4

BookingID (Access inserts number) BookingID (Access inserts number) BookingDate 6/18/2021 BookingDate 6/19/2021 TourID HC01 TourID AT02 AgentID 109 AgentID 109 NumberPersons 2 NumberPersons 4

6. Save and then close the Bookings table. 7. Create a one-to-many relationship that joins the Agents table with the Bookings

table and cascade fields and records. (You determine the field that joins the two tables.) Hint: If you receive an error message when establishing a relationship, check to make sure that all the field types and field sizes are similar in the fields you are trying to join.

8. Create a one-to-many relationship that joins the Tours table with the Bookings table and cascade fields and records. (You determine the field that joins the two tables.)

9. Create a relationship report and then close the Relationships window without saving the changes.

Marquee Series © Paradigm Publishing, LLC Access 365, 2019 Edition: Section 2, Challenge 1 Hands On Activity