As the manager of Riverwalk, a multispecialty health clinic, you have created a database to manage the schedules that connect each healtheare provider with the nurses that provider needs to efficiently handle patient visits. In this exercise, you will modify the tables to improve the database. a. Start Access. Open the IL_AC_5-3.accdb database from the location where you store your Data Files and save it as IL_AC_5_Riverwalk. Click Enable Content if a yellow Security Warning message appears. b. You want to add Lookup properties to the NurseNo field in the Scheduleitems table. First you will make sure it is not currently participating in any relationships as the Lookup Wizard will not work on a field that is currently used in a relationship. Open the Relationships window, right-click the existing relationship between the Nurses and Scheduleltems tables, click Delete and Yes to delete the existing relationship, then save and close the Relationships window. c. Open the Scheduleltems table in Design View, then start the Lookup Wizard for the NurseNo field. Select all of the fields from the Nurses table and sort them ascending order on the Nursel. Name field. Hide the key column, enable data integrity, and use Nurse as the label. Click Yes when prompted to save the table. The field name changed from NurseNo to Nurse in Design View of the Scheduleltems table and in the Relationships window, and a one-tomany relationship with referential integrity was established between the Nurses and Scheduleltems tables. d. Close the Relationships window and the Scheduleltems table. Open the Nurses table in Datasheet View and add your own name as a new record. e. Close the Nurses table and open the Scheduleitems table in Datasheet View. Add your name to the Nurse lookup field for the first record (TransactionNo 28), then close the Scheduleltems table. f. Open the Providers table in Design View. The DrPA field has only four valid entries: MD, DO, PA, or NP. (MD stands for medical doctor, DO for doctor of osteopathic medicine, PA for physician assistant, and NP for nurse practitioner.) You will use the Lookup Wizard to add these values to a Iookup list. Start the Lookup Wizard for the DrPA field, choose the "I will type in the values that I want" option, then click Next. g. In Col1, enter four rows as shown in FiGune 5-21. Change the title to Degree and check the Limit to List check box. h. Save and view the Providers table in Datasheet View. Add your own last name to the datasheet with a Degree value of DO, then save and close the Providers table. i. Open the ScheduleDate table in Design view and add >=#8/26/2021 \# as a Validation Rule to the ScheduleDate field which requires all entries to be on or after 8/26/2021. Add the text All entries must Independent Challenge 1 (continued) be on or after 8/26/2021 as the Validation Text property. Save and test the data, then close the ScheduleDate table. j. Open the Nurses table in Design View and change the Indexed property to Yes (Duplicates OK) for the Nursel Name field. k. Add a field named NursePhoto with an Attachment data type and a field named NurseEmail with a Hyperink data type to the end of the field list. 1. Save the Nurses table and switch to Datasheet View. In the record with your name, attach a photo of yourself or the Support_AC_5_nurse.png file found in your Data Files to the NursePhoto Attachment field. Add your school email address to the NurseEmail field. m. Close the Nurses table, compact and close the IL_AC_5_Riverwalk database, then exit Access
