creating files with Access and Excel

timer Asked: Apr 26th, 2018
account_balance_wallet $15

Question Description

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 XXX.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 NameData TypeSize / TypePrimary Key?Description
    TourIDAutoNumberLong IntegerYesTour ID key field
    TourNameShort Text22Name of Tour
    CapacityNumberLong IntegerMaximum Per Tour Group
    ExpenseCurrencyCurrencyCost to Provide Tour
    TicketPriceCurrencyCurrencyTicket Price
    FuelSurchargeYes/NoFuel Surcharge Applicable
    SurchargeAmountCurrencyCurrencyAmount of Fuel Surcharge
    Figure 1 - tblTours Structure
  6. Input 7 records of data into tblTours
    1Canyon Narrows61955No2.10
    2Eagles Landing62255Yes3.25
    3Painted Canyon182675No1.50
    4River Float123165No1.00
    5Silver Springs122145Yes2.50
    6Sunset Photo241530Yes3.10
    7Valley Explorer501025Yes4.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 text pages 75 - 81.

  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 pages 104 - 113 in your Access text.
  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 XXX.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: 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"
  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 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.
    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 9 - Import Access Table into Excel

  1. 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.
  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
    tour calculations
  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:
    rows 11 - 13
  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.
    All ReservationsLight Gray 25%, Background 2, Darker 25%
    CalculationsBlue, Accent 1, Lighter 40%
    FilteredGold, Accent 2, Lighter 40%
    Pivot TableGreen, Accent 6, Lighter 40%
    Tour ChartOrange Accent 2, Lighter 40%
    view Theme colors
  7. Upload your Exam 5 files by clicking the
    Exam 5 Access link for your Database - XXX.XXXXXX_Exam5 and
    Exam 5 Excel link for your Spreadsheet - XXX.XXXXXX_Exam5

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors