Part 1 - ACCESS - Update and add content to the database
Each student has a unique file for this assignment. Click the button to agree to the conditions of this assignment and create your data file downloads.
Save the file with the name XXX.XXXXXX_Exam5 (your file should automatically be named)
Start Access and open the database file you downloaded. If you see the yellow security warning then click the Enable Content button.
Open the tblCustomers and verify that there are 3830 records in the table. - View Example
Create a new table named tblTours with the following database structure
Size / Type
Tour ID key field
Name of Tour
Maximum Per Tour Group
Cost to Provide Tour
Fuel Surcharge Applicable
Amount of Fuel Surcharge
Figure 1 - tblTours Structure
Input 7 records of data into tblTours
Figure 2 - tblTours Data
Create a filter to display only the records which have FuelSurcharge set to Yes
Save the changes to the table
Part 2 - Import Data
Import the tblReservations text file you downloaded in step 1 as a table named tblReservations. - Do not add an additional field as a key field or index during the import. - You will select a key field from the existing data in the imported file contents the table design update. - Any ImportErrors tables will be a deduction and indicate you had an error during the import. B - Be sure to correct the import error and delete and ImportErrors tables if they exist.
Part 3 - Update Table Design
Modify table design following for tblReservations following database design methods covered in your text. See your Access text pages 75 - 81.
Set appropriate field types, format, and sizes.
Analyze the fields in the table and select the field which would be a logical choice for key fieldfrom one of the fields you imported.
Save changes to your tables.
Part 4 - Create Relationships
Note: Use the following as a guide in creating your relationships.
Customers make reservations.
The reservations are for tours. For additional information see pages 104 - 113 in your Access text.
Create a relationship between the tables by analyzing the fields in each table and creating appropriate relationships.
Add Enforce Referential Integrity and Cascade Update Related Fields to all relationships.
Save the changes to the relationships
Part 5 - Create 4 Queries
Note: Sorting queries must be done in the query design grid not on the datasheet.
Create and save a query named qryAllReservations. This query will include the following fields in this order, CustomerID, TourName, FirstName, LastName, State, Adults, ReservationMonth, PaymentType. Sort the query on the CustomerID field in ascending order.
Create and save a query named qryNMMayRiverFloatCustomers. This query will include only River Float reservations in May from New Mexico (NM) customers displaying only the following fields in the query results: FirstName, LastName and number of Adults. Sort the query by LastName in ascending order.See example (your data may be different)
Create and save a query named qryMayTourTotals. This query should be grouped by Tour Names with the total number of Adults having reservations in May and listing the number of adults in descending order. Change the Caption of the SumOfAdults to Adults. The query results should only display the Tour Name and number of Adults fields. See example (your data may be different)
California requires companies to collect tax if they make sales and have an office in California. The tour company has an office in California so you need to calculate how much to collect. The California tourism tax is $1.85 for each adult ticket sold to Californians.
Create and save a query named qryCaliforniaTax from tblCustomers and tblReservations tables for the total of Adults from California.
Add a calculated field to the qryCaliforniaTax query by adding an expression multiplying the total of the of Adults from California times the $1.85 tax amount.
Set the name of the calculated field heading to Tax and and the caption of the SumOfAdults to Adults.
Format the calculated Tax field as currency. See example (your data may be different)
Part 6 - Create a Form
Create a form named frmReservations including only the following fields: ReservationID, FirstName, LastName, State, TourName, Adults, ReservationMonth, PaymentType.
Select to view your data by tblCustomers as a single form.
Change the title of the form to Red Rock Reservations. See example
Part 7 - Export Data to Excel, set option to compact on close
To perform further analysis and create a chart you need to export the reservations to a new workbook.
Export the query qryAllReservations to Excel
Name the new exported workbook file XXX.XXXXX_Exam5
Set the compact on close option for the database.
Part 8 - EXCEL - Format Workbook
Start Excel and open the spreadsheet you exported from Access Verify that you have 3830 rows of data, if not then you have an error in your Access export.
Add the following 3 document properties via the Document Properties panel. Author: XXX.XXXXX Title: Exam5 Spring 2018 Comments: location where you completed the exam examples if you completed it at home then list - "my home system" if you complete it on campus then list the room and computer number using room E206 system 32 would be entered as - "E206 system 32" using the Tech Mall system 15 then list - "Tech Mall system 15"
Change the worksheet tab name to All Reservations
Insert a new row 1 (moving all the data down 1 row)
Enter the title All Reservations into the inserted row, add a background color and increase the size.
Format the column titles in row 2 to look exactly like the example below using line breaks and setting the column widths as follows: Customer ID 11, Tour 15, First Name11, Last Name 11, State 8, Adults 8, Reservation Month 11, Payment Type 15. These widths are in pixels not inches. Note: Adding spaces to make the titles look correct will be graded as an error.
Insert the current date header/footer element in the left header area and insert the file name header/footer element in the center header area and type your name in the right header area.
Freeze rows 1 & 2 and column A so they do not scroll.
Part 9 - Import Access Table into Excel
Import all tblTours records from your Exam 5 database - XXX.XXXXXX_Exam5 into a new worksheet. If you do not import the database table using the import command there will be a deduction.
Adjust the column titles adding a space to split the words.
Calculate the Profit for each tour in column H. The profit is the difference between the Ticket Price and the Expense.
Calculate the Surcharge Total in column I. The surcharge total is the Capacity times the SurchargeAmount.
Insert a column between H and I. Enter a column title of Total Profit.
Calculate the Total Profit for each tour where the total profit is the Profit times the Capacity.
Hide columns F and G.
Insert a new row 1 moving all the data down 1 row
Enter the title Tour Calculations in row 1 and format it the first 2 rows in your worksheet should look like this
Calculate the following for the Surcharge Total column, Minimum in row 11, Average in row 12, Maximum in row 13. Should look like this:
Format the values for the Minimum, Average, and Maximum with 0 decimal places
Name the worksheet tab Calculations
Part 10 - Create the Filtered Reservations Worksheet
Copy the All Reservations Worksheet into a new worksheet
Change cell A1 text to Filtered Reservations and set the background using a different color than the All Reservations worksheet
Create a table from the data in the Filtered Reservations worksheet
Sort the Sales data by Tour in ascending order
Add a filter to display only River Float sales where the number of Adults is 1. See example
Change the name of the worksheet tab to Filtered
Part 11 - Create the Pivot Table
Create a Pivot Table from the All Reservations worksheet
Set the pivot table as follows:
ReservationMonth as the Row Labels
Sum of Adults as the Values
Tour and State as the Report Filters
Apply a filter to display only the River Float reservations.