creating access and excel files

Part 1 – ACCESS – Update and add content to the database

1. 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.
   
2. Save the file with the name xxxx.xxxxxx_Exam5 (your file should automatically be named)
3. Start Access and open the database file you downloaded.
   If you see the yellow security warning then click the Enable Content button.
4. Open the tblCustomers and verify that there are 3830 records in the table. – View Example
5. Create a new table named tblTours with the following database structure
   

   Field Name	Data Type	Size / Type	Primary Key?	Description
   TourID	AutoNumber	Long Integer	Yes	Tour ID key field
   TourName	Short Text	22		Name of Tour
   Capacity	Number	Long Integer		Maximum Per Tour Group
   Expense	Currency	Currency		Cost to Provide Tour
   TicketPrice	Currency	Currency		Ticket Price
   FuelSurcharge	Yes/No			Fuel Surcharge Applicable
   SurchargeAmount	Currency	Currency		Amount of Fuel Surcharge

   Figure 1 – tblTours Structure

6. Input 7 records of data into tblTours
   

   TourID	TourName	Capacity	Expense	TicketPrice	FuelSurcharge	SurchargeAmount
   1	Canyon Narrows	6	19	55	No	2.10
   2	Eagles Landing	6	22	55	Yes	3.25
   3	Painted Canyon	18	26	75	No	1.50
   4	River Float	12	31	65	No	1.00
   5	Silver Springs	12	21	45	Yes	2.50
   6	Sunset Photo	24	15	30	Yes	3.10
   7	Valley Explorer	50	10	25	Yes	4.50

   Figure 2 – tblTours Data

7. Create a filter to display only the records which have FuelSurcharge set to Yes
8. Save the changes to the table

Part 2 – Import Data

1. 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 e-text chapter 2 SLO 2.1 & 2.2 to review setting PK and field properties.

1. Set appropriate field types, format, and sizes.
2. 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.
3. 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 youre Access e-text chapter 2 SLO 2.6.

1. Create a relationship between the tables by analyzing the fields in each table and creating appropriate relationships.
2. Add Enforce Referential Integrity and Cascade Update Related Fields to all relationships.
3. 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.

1. 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.
2. 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)
3. 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.

4. Create and save a query named qryCaliforniaTax from tblCustomers and tblReservations tables for the total of Adults from California.
5. 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.
6. Set the name of the calculated field heading to Tax and and the caption of the SumOfAdults to Adults.
7. Format the calculated Tax field as currency. See example (your data may be different)

Part 6 – Create a Form

1. Create a form named frmReservations including only the following fields:
   ReservationID, FirstName, LastName, State, TourName, Adults, ReservationMonth, PaymentType.
2. Select to view your data by tblCustomers as a single form.
3. 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.

1. Export the query qryAllReservations to Excel
2. Name the new exported workbook file xxxx.xxxxx_Exam5
3. Set the compact on close option for the database.
4. Exit Access

Part 8 – EXCEL – Format Workbook

1. 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.
2. Add the following 3 document properties via the Document Properties panel.
   Author: xxxxx.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“
3. Change the worksheet tab name to All Reservations
4. Insert a new row 1 (moving all the data down 1 row)
5. Enter the title All Reservations into the inserted row, add a background color and increase the size.
6. 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 Name 11, 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.
   
7. 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.
   
8. Freeze rows 1 & 2 and column A so they do not scroll.

Part 9 – Import Access Table into Excel

1. Import all tblTours records from your Exam 5 database – xxxxxx.xxxxxx_Exam5 into a new worksheet.
   If you do not import the database table using the import command there will be a deduction.
2. Adjust the column titles adding a space to split the words.
3. Calculate the Profit for each tour in column H. The profit is the difference between the Ticket Price and the Expense.
4. Calculate the Surcharge Total in column I.
   The surcharge total is the Capacity times the SurchargeAmount.
5. Insert a column between H and I. Enter a column title of Total Profit.
6. Calculate the Total Profit for each tour where the total profit is the Profit times the Capacity.
7. Hide columns F and G.
8. Insert a new row 1 moving all the data down 1 row
9. Enter the title Tour Calculations in row 1 and format it
   the first 2 rows in your worksheet should look like this
   
10. Calculate the following for the Surcharge Total column, Minimum in row 11, Average in row 12, Maximum in row 13. Should look like this:
    
11. Format the values for the Minimum, Average, and Maximum with 0 decimal places
12. Name the worksheet tab Calculations

Part 10 – Create the Filtered Reservations Worksheet

1. Copy the All Reservations Worksheet into a new worksheet
2. Change cell A1 text to Filtered Reservations and set the background using a different color than the All Reservations worksheet
3. Create a table from the data in the Filtered Reservations worksheet
4. Sort the Sales data by Tour in ascending order
5. Add a filter to display only River Float sales where the number of Adults is 1. See example
6. Change the name of the worksheet tab to Filtered

Part 11 – Create the Pivot Table

1. Create a Pivot Table from the All Reservations worksheet
2. Set the pivot table as follows:

• ReservationMonth as the Row Labels
• Sum of Adults as the Values
• Tour and State as the Report Filters

1. Apply a filter to display only the River Float reservations.
2. Name the worksheet tab Pivot Table. See example

Part 12 – Create a Tour Chart

1. Create a Column Chart of the monthly totals from the Pivot Chart worksheet.
2. Use the Move Chart command to move the chart to a new new worksheet and name it Tour Chart
3. Add / Change the Chart Title above the chart to Monthly Totals
4. Delete the chart legend. See example

Part 13 – Finish and Submit

1. Create a new worksheet.
2. Add a title to the worksheet and worksheet tab using the name Links.
3. Move the Links worksheet tab so it is the first tab on the left.
4. Add the name for each worksheet and then link the name to the respective worksheet.
   You don’t need to have a link to the Tour Chart.
5. Format your links worksheet to look professional.
6. Change the color of each worksheet tab as follows and arrange them in this order from left to right with Links being first and Tour Chart last.
   

   Links		Red
   All Reservations		Light Gray 25%, Background 2, Darker 25%
   Calculations		Blue, Accent 1, Lighter 40%
   Filtered		Gold, Accent 2, Lighter 40%
   Pivot Table		Green, Accent 6, Lighter 40%
   Tour Chart		Orange Accent 2, Lighter 40%

   view Theme colors

7. Upload your Exam 5 files by clicking the
   Exam 5 Access link for your Database – xxxxx.xxxxx_Exam5 and
   Exam 5 Excel link for your Spreadsheet – xxxxx.xxxxx_Exam5