Microsoft Access Semester Project



Microsoft Access Semester Project

Appalachian Treks

Spring 2017

Overview

This project is based on a case from the New Perspectives textbook. The project has been adapted and expanded by the instructor to meet the course objectives.

Molly and Bailey Johnson, sisters and avid outdoors people, recently opened a hiking business called Appalachian Treks. As the business has grown, the sisters have seen the need for a better system to track clients as well as services. Currently Appalachian Treks offers six tours of varying lengths and difficulties.

Molly currently handles the bookkeeping and paperwork for the company. She has asked you to help design a database that will store information about the clients the business serves and the tours it provides as well as manage tour reservations.

Below you will find a list of requirements for this database. Some requirements are very specific while others require you to apply your expertise, judgment, and critical thinking.

Project Objectives

▪ Plan, create and edit tables (20 points)

▪ Create Relationships (2 points)

▪ Create and format forms (15 points)

▪ Interact with a database by entering data (2 points)

▪ Interact with other Office applications by importing & exporting data (9)

▪ Create queries (16 points)

▪ Create and format reports (20) points)

▪ Create a switchboard (10) points)

▪ Print selected records and reports (6 points)

Due Date/Submission

This completed project is due by 11:59 p.m. on Monday, May 1, 2017. A 10 point bonus can be earned by submitting the project by 11:59 p.m., Tuesday, April 25. Late projects may be submitted through 11:59 p.m., Thursday, May 4, but a 20 point per calendar day deduction will apply to any work submitted late.

Turn in your project by uploading your Access file to the module labeled Semester Project under Assignments in NS Online. You will also be asked to print out hard copies of certain documents throughout this project. Your printouts should be collected in a three-prong paper folder and turned in to your instructor by the specified due date and time. Include a title page with the following information centered on the page. Place all printouts in the order printed behind the title page.

Title Page Layout:

Your Name

Accounting Databases

Semester Project

Spring 2017

Grading

This project is worth 10% of your overall course grade. The project is worth a total of 100 points broken out as shown under Project Objectives above. Each project element will be graded based on completeness, accuracy, application of database concepts, and presentation (including user interface).

Project Requirements/Instructions

Database

Download and save the [Your Last Name]Hikings17 database emailed to you by your instructor.

Tables

Overall Table Requirements

• Create fields that efficiently and effectively store the data for each table.

• Define descriptive field names and apply appropriate properties for the fields in each table.

1. Design and create a table in Design View named Hikers to store client information.

a) Current client data is found in the spreadsheet labeled Hikers which can be found under the Project link on the Lessons page. Use the current hiking clients list as a basis for the information to be stored in this table.

b) Create appropriate field names and properties to store hiker data.

c) There will only be one record per client in the Hikers Table.

d) Most clients reside in North Carolina; therefore, the default value for the state field should be NC.

e) The level field indicates the skill level of the hiker. There are three skill levels. Set a validation rule so that the only acceptable entries for this field are 1, 2 or 3.

f) Add an attachment field named HikerProfile. This field will be used to store a survey on hikers that will help to assess the hiker level.

2. Design and create a table in Design View named Tours to store information about the tours offered by the company.

a) A list of tours offered by Appalachian Treks is attached to the project page. Use the list of tours as a basis for designing this table.

b) Create appropriate field names and properties.

c) Each tour is unique.

3. The Reservations table has already been created and populated with data. This table is used to store information about who has requested a tour, which tour was requested, and the tour date.

a) Adjust the TourName field to include a combo box with a value list displaying each tour name. See the Tours spreadsheet for a list of Tour Names.

b) Test your value list. When you go to Datasheet View, you should get a drop-down menu in the Tour field.

c) The primary key for this table was omitted in design. The ReservationID field should be set as the primary key.

Import Data

1. Import (or copy) the data from the Hikers Excel spreadsheet to the Hikers table in Access. The Hikers spreadsheet is found under the Project link on the Lessons page.

2. Import (or copy) the data from the Tours Excel spreadsheet to the Tours table in Access. The Tours spreadsheet is found under the Project link on the Lessons page.

Relationships

1. Close all open objects.

2. Define and save appropriate relationships between all tables in the database.

Forms

Overall Form Requirements

• Use a pleasing format (font style, size, color, bold, etc.).

• Remove all rectangles around textboxes and remove alternate background colors.

• Include explanatory text when appropriate.

1. Design and create a custom entry form for client data from the Hikers table with a subform for reservations data.

a) Name the form HikersAndReservationsFrm.

b) Set the Record Source to the Hikers table.

c) The initial layout of the form should match that shown in Exhibit A at the end of the instructions.

d) Close the main form and open the subform in datasheet view. Autosize the subform columns. Close the subform and open the main form.

e) Clients who have booked 5 tours with Appalachian Treks get a 20% discount on the 6th tour. Add a calculated field on the main form that counts the number of reservations shown on the subform (HINT: You will have to add a calculated field to the subform to count the reservations – For more information, review Module 6, pages 356-360). Place the calculated field in a suitable location on the main form. Make sure the label for the calculated field is appropriate.

f) Add the following formatting to your form. The formatting is not shown in the example. Use your own judgment and style.

i. Add a form header with an appropriate title.

ii. Include a background color for the detail section of the main form with a coordinating background color for the form header.

iii. Change the text color of the labels to a color coordinating with the background (do not leave the text color black).

iv. Add a two-point dividing line somewhere on the form.

f) The tab order for the form should be as follows:

HikerID, Level, Phone, Hiker First Name, Hiker Last Name, Address, City, State, Zip, Subform. Skip the Hiker Profile field, but allow users to select it. Skip the calculated field and do not allow users to select it.

Enter Data

1. Using the HikersAndReservations form, enter yourself as a client of Appalachian Treks.

a) Your Hiker ID is 501.

b) Your skill level is 1.

c) Be sure to enter your own name but data for all other fields may be fictitious.

2. Using the Find command, locate the record for Jack Smith.

a) In the Attachment field, add the document called Hiker553 to the record for Jack Smith. This document contains the hiker skill level assessment for this client and can be found in the project information page.

b) Print the record for Jack Smith.

Queries

Design queries to help the business generate the information described below. Some queries will be used as “reports” themselves. Other queries are the basis for reports to be designed in the Reports object.

1. Molly wants to be able to retrieve information on specific tours at any time by specifying the tour she wants to see.

a. Using all tables, create a query called Tours Parameter.

b. Include the tour name, tour description, and hiker name in the results of this query.

c. Be sure to use a query feature that lets Molly specify the tour she wants to view when the query is run.

2. Molly wants a listing of all reservations by client.

a. Design a query called ReservationsByClientQry based on all tables.

b. Include the hiker name, tour date, tour name, and price fields.

c. Sort the query results by hiker last name.

d. Create a parameter criteria for the tour date field that allows the user to specify the beginning and ending dates for the report. HINT: Use two parameters within the BETWEEN/AND operators.

e. Test your query by running the query with the following dates: Beginning Date – 10/1/2017; Ending Date – 10/31/2017. You should get 7 records

f. In Datasheet view, turn on the Sum function and sum the price field. Check figure: $1,075.

3. Molly wants to see the number of hikers for each tour day organized by tour name.

a. First create and save a query called CrosstabData based on all three tables. Include the TourDate, TourName, and HikerID fields in the query results.

b. Using the Crosstab Query Wizard, design a query called HikersByDateQry based on the CrosstabData query. The tour date will be the row heading and the tour name will be the column heading. Count the HikerIDfield.

c. Save your changes, run and print the crosstab query results.

4. Molly wants to see a list of hikers with skill levels or 2 or 3 who have signed up for a tour.

a. Create a query called ExpertHikers based on the Hikers and Reservations tables. Include the hiker name fields as well as the Phone, Level and ReservationID fields in the query results.

b. Include criteria that limits the query results to hikers with a skill level of 2 or 3.

5. Molly needs information to prepare a tour reservation report.

a. Design a query called ReservationsDataQry based on all three tables.

b. Include the tour date, tour name, reservation ID, and level fields in the query results.

c. In a blank cell, add an expression that concatenates the hiker first and last name. Name this field “HikerName”.

d. A report will be based on this query.

6. Molly needs information to prepare a report on tour revenue.

a. Design a query called RevenueDataQry based on the Reservations and Tours tables.

b. Include the TourName, TourDate, Price, SupplyCost and ReservationID fields in the query results.

c. Turn on the Totals function and count the number of reservations. Change the name for the counted field to NumRes. Leave all other fields as Group By.

d. A report will be based on this query.

Generate Information – Reports

Overall Report Requirements

• Use a pleasing format (font style, size, color, bold, etc.).

• Remove all rectangles around textboxes and remove alternate background colors.

• Ensure the page width is correct and that each section length is correct.

1. Reservations report. Design a custom report that displays information on tour reservations.

a. Base the report on the Reservations Data query.

b. Add a TourName Header and Footer (use the Group & Sort tool.)

c. Place the TourName field in the TourName Header.

d. Place the tour date field in the Detail section. Ensure that the tour date is not duplicated.

e. Place the hiker name to the left of the tour date in the Detail section.

f. Place the Level fied to the left of the hiker name in the Detail section.

g. Add an unbound Text Box in the TourName Footer. Place a formula to count the tour participants for each tour. In the Properties box, change the name of this field to NumHikers and label this field “Total Hikers for Tour”.

h. Apply appropriate design and layout for this report including a heading.

i. Print this report.

2. Revenue report. Design a custom report that displays information on tour revenue and costs.

a. Base the report on the RevenueData query.

b. Add a TourName Header and include a TourName footer (use the Group & Sort tool.)

c. In the TourName Header, include the TourName field. Remove the field label from the TourName field.

d. Place the TourDate, NumRes, Price, and SupplyCost fields in a single row in the Detail section.

e. Ensure that the TourDate field is not duplicated.

f. Place the labels for the TourDate, NumRes, Price, and SupplyCost fields in the TourName Header and adjust the label contents s so that they are meaningful.

g. Place an unbound text box in the TourDate footer aligned with the NumRes text box. Label this field “Total Reservations” (you may name this field and all other unbound text fields any appropriate name but be sure to refer to the correct field name in all related expressions).

h. Enter an expression in the Total Reservations text box that sums the number of reservations.

i. Place a second unbound text box in the TourDate footer underneath the total reservations field. Label this control “Total Revenue”.

j. In the unbound textbox, enter an expression that calculates total revenue as Price * Total Reservations.

k. Place a third unbound text box in the TourDate footer underneath the Total Revenue field. Label this control “Total Cost”.

l. In the third unbound textbox, enter an expression that calculates total cost as SupplyCost * Total Reservations.

m. Place a fourth unbound text box in underneath the Total Cost field. Label this control “Gross Profit”.

n. In the fourth unbound textbox, enter an expression that calculates gross profit as total revenue less total cost.

o. Add a report header and footer (HINT: right-click on the report and select Report Header/Footer).

p. Place an appropriate report label in the report header.

q. Apply appropriate layout and format for this report.

3. Export Data

Molly has asked that you send her the information on expert hikers.

1. Using Microsoft Word, create a short memo to Molly indicating you are sending the requested information. Use proper formatting, grammar and sentence structure.

2. Export the ExpertHikers query results to this document. Print the memo.

Switchboard

1. Develop a switchboard to operate this database.

a. Design appropriate macros to run the switchboard.

b. Include only the objects that Molly will need to run (HINT: if a query is used only as the basis for a report, do not include the query in the Switchboard.)

c. Set the switchboard so that it appears when the database is opened.

Exhibit A – Initial Layout of HikersAndToursForm

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download