1. Create a Folder on your Desktop named Exam. 2. Downl…
1. Create a Folder on your Desktop named Exam. 2. Download and save both following files to your ‘Exam’ folder: SP26_Access_HO4_Template.accdb SP26_HO4_Data.xlsx Filename, Tables & Relationships — 45 Points Use the SP26_Access_HO4_ Template.accdb database file that you downloaded from Blackboard for this exam. Rename or “Save As” this database file to your last name+your first initial+.accdb. For example, John Smith’s file name would be smithj.accdb. Save your file in the Exam Folder that you created on your desktop. 1. Open your newly renamed database file in Access. This database will be used to track Frontier Travel, Inc.’s Customers, Packages, and Reservations that have been made so far for the mid July – early September 2021 season. 2. A new table, Reservations has been provided for you. This table currently has no records so you will need to Append a Copy of the Records from the Excel file provided. The file includes 48 reservation data records contained on the Reservations sheet. The Excel file is named SP26_HO4_Data.xlsx. Make any necessary changes to ensure that the table settings are as indicated below. (tip: Do NOT copy/paste the data from the Excel file, use import from the MS Access menu/ribbon) FIELD NAME DATA TYPE Property Settings ReservationID Short Text Primary Key FieldField Size = 6Format = Force to upper case CustomerID Use the same data type setting already established in the related field located in the Customers table Use the same field size setting from related field in the Customers table CruiseNum Short Text Field Size = 5 PartyNum Number Field Size = IntegerFormat = FixedDecimal Places = 0Caption = Party Size BookingAgent Short Text Field Size = 15 3. Next, in your Reservations table, insert a new field immediately after the CruiseNum field and name it PaidInFull. a) Enter all of the following description text: Is reservation paid in full? Check the box if YES. b) Make the data type Yes/No, the format Yes/No, and set the Default Value to Yes. c) In the Datasheet View, indicate that all records with a PartyNum (Party Size) greater than 6, have been paid in full. Eleven records should be checked. d) Sort the table on CustomerID, ascending as the primary sort. e) Ensure that all Field Names and Contents are visible in the Datasheet View. f) Using the same Excel file as in Step 2, Import the CruiseInfo source data into a new table in the current database” and name it CruiseInfo. Change all of your table settings as indicated below. FIELD NAME DATA TYPE Property Settings CruiseNum Use the data type setting from related field in the Reservations table Primary Key FieldUse field size setting from related field in the Reservations table Destination Short Text Field Size = 20 DeparturePort Short Text Field Size = 20 TripCost Currency Format = CurrencyDecimal Places = 0 DepartureDate Date/Time Format = Short DateRequired = Yes TripLength Number Field Size = IntegerFormat = FixedDecimal Places = 0Default Value = 6 CruiseLine Short Text Field Size = 20 ** NOTE: Given our exam time constraints, you are not being asked to provide any descriptions / documentation for the above table. However, remember that as a sound business practice, you should always document your table structure in the Description area. g) Ensure that all Field Names and Contents are visible in Datasheet View. 4. Next, open your Customers table and make the following field changes: a) Set CustomerID as the Primary Key. b) Force CustState to upper case. c) Change the field size of CustZip to 10. d) Change the field size of CustPhone to 14. e) Ensure that all Field Names and Contents are visible in Datasheet View. 5. In the Datasheet View of the Customers table, input all of the following information into a new record: CustomerID = 20111 CustLastName = Your Last Name CustFirstName = Your First Name CustAddress = 3211 Providence Drive CustCity = Anchorage CustState = AK CustZip = 99508 CustPhone = 9077219999 6. In the Datasheet View of the Customers table, sort the records by both CustLastName (primary sort key) and CustFirstName (secondary sort key) in ascending order. 7. Save and close all table objects. 8. Create a relationship between the Customers and Reservations tables using the appropriate field. Enforce Referential Integrity. 9 . Create a relationship between the CruiseInfo and Reservations tables using the appropriate field. Enforce Referential Integrity and ensure that you do not have any extra tables in your Relationships Window. Close and save your Relationships. Forms, Subform, and Query – 42 Points 1. Using the Form Wizard, create a main form from your CruiseInfo table with a subform from your Reservations table. Use all of the fields from both tables; however, do not duplicate the one related field from the Reservations table. View data by CruiseInfo. Use the Tabular layout for the subform. Name your main form Cruise Information, and your subform Reservations Subform. 2. Switch to the appropriate View for the forms and make the following changes: a) Apply the Retrospect Theme to this Object Only and Green Theme Colors to this Object Only. b) Change the label in the main form header to July – September 2021 Cruise Information. Increase the size of the label object to the width of the form and center the label text. c) Move the DepartureDate, TripLength, and CruiseLine fields up and to the right of CruiseNum, Destination, and DeparturePort respectively. d) Move the subform label and subform object “up” to remove the gap just created. e) Reduce the width of the TripCost field to be the same width as the field above it. f) Ensure that all Field Names and Contents are completely visible in the form and subform. g) Dave is being assigned two of Deborah’s existing reservations. Using the Form View, indicate that Dave has been assigned to all reservation records for CruiseNum 20003. And that those reservations indicate that they have been PaidInFull. There will be two reservations assigned to Dave. 3. Apply Conditional Formatting to your TripCost field that will emphasize all values that are $2000 or more, with a Bold and Dark Red (1st column, bottom row) Font Color with a light gray 3 (1st column, 4th row under Standard Colors) Background Color. 4. **Extra Credit: With your Cruise Information main form and Reservations subform open in Design View, add a Text Box control object in the Detail area of the mainform below CruiseLine. This object should compute the Daily Cost for each trip for the Cruise Information currently being viewed in the main form (use TripCost divided by TripLength). A text label describing the control should read DailyCost. Add a Currency format with two decimal places to the calculated control. Adjust the width of the DailyCost field to be the same width as the field above it. 5. The first record (CruiseNum 20001) in your mainform and subform should appear similar to the figure below. 6. Save and close all of your tables and forms. ** Ensure that all field names and all field contents are completely visible in the Datasheet View of all of the following three queries. 7. Frontier Travel wants to schedule a pre-trip meeting place for the customers who live in Chugiak and those who live in Eagle River, who will be departing on their trip from San Juan on July 22. Create a query using Design view that shows only the fields: CustomerID, CustLastName, CustCity, CustPhone, and PartyNum (in that order). Sort on CustLastName in Ascending Order and save your query as SanJuanJul22. NOTE: Your query results should show 2 records. 8. Frontier Alaska needs summary information from their database. Create a query using Design view that displays a list of each CruiseInfo Departure Port, the count of those ReservationIDs, and the sum of PartyNum currently in the database. (You will need to use the COUNT Group By and SUM Group By options.) Sort by Sum of PartyNum in Ascending order and save your query as ReservationsByDeparturePort. NOTE: Your query results should show 7 records, with San Diego showing a Count of 15 Reservations and a Sum of 76 for PartyNum. 9. Frontier Alaska needs a list of their customer reservations for those who are scheduled to depart in August 2021, and that have not been paid in full. Create a query in Design View that lists all customer reservations that are scheduled to depart in August 2021, and that have not been paid in full yet. Show only the following fields: ReservationID, PartyNum, TripCost, DepartureDate, and CruiseNum (in that order). Next, create and show a calculated field that displays the Total Cruise Price for each record. Name the field TotalCruisePrice. NOTE: The calculation to use is the PartyNum multiplied by the TripCost. Format this field as Currency with 0 Decimals. Sort the Query by the DepartureDate field in Ascending order and the TotalCruisePrice field in Descending order. Save your query as UnpaidAugCruises. NOTE: Your query results should show 15 records. Report and Compact – 13 Points 1. Use the Report Wizard to create a report named ReservationsByAgent. 2. Include the following fields from the appropriate tables (in that order): BookingAgent, ReservationID, PartyNum, PaidInFull, CustomerID, CustLastName, CruiseNum, and Destination. Use the following parameters in your report: a) View data by the Reservations table. b) Group by BookingAgent. c) Sort by CruiseNum in ascending order. d) Use Stepped layout and Landscape orientation. 3. Switch to the appropriate View for the report and make the following changes: a) Apply the Facet Theme to this Object Only, and the Green Yellow Theme Colors to this Object Only. b) Change the label in the report title to Reservations by Booking Agent, and center across the width of the report. Also, include a separate label with your name directly below the report title and centered across the width of the report. 4. Center-Align all values in the PartyNum (Party Size) field and ensure that all Field Names and Contents are visible in the Report View. 5. Ensure that all Field Names and Contents are visible in the Report View. 6. Save and close the report. 7. Compact & Repair and then close the database. 8. In the answer box below, attach your MS Access file from the Exam folder (e.g., SmithJ.accdb) below. (Check the timestamp of the file before you attach it. It should show the current time.) Screenshot 2025-02-26 124438.png 9. Delete the exam folder on the desktop and all files in it. 10. Empty the Recycle Bin. You MUST delete the exam from your home PC if using Honorlock, as this will be checked on the video.