Excel/Access Database Exam

  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 Jon.Smith_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

    Save your time - order a paper!

    Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlines

    Order Paper Now
  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 Jon.Smith_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: Jon.Smith
    Title: Exam5 Spring 2011
    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.
    row format
  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.
    Header Footer
  8. Freeze rows 1 & 2 and column A so they do not scroll.

Part 9Import Access Table into Excel

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

Part 10Create 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 13Finish 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

"Our Prices Start at $11.99. As Our First Client, Use Coupon Code GET15 to claim 15% Discount This Month!!":

Get started