419/519 Database Applications and Information Management
Module 7: Challenge Problem #1 – (34 points)
Name: ______
Directions:
· You should work independently, but may access the book resources, the internet and all course website materials. Please do not collaborate with others for this Challenge Problem.
· Please remember to save your work on a regular basis.
· Throughout the Challenge Problem you will be completing sets of individual activities (Activities 1 – 3)
· You will NOT be submitting the completed database files, instead you will screen capture your completed work, paste it into a Word document, and submit the Word document once you have all of the contained activities completed.
o Please keep a copy of all of your completed work (including the database file) for your own records and possible future reference.
· The Word document will be quite lengthy, please be sure to clearly label each part and place your solutions in the same order in which they are presented throughout the Challenge Problem. Name and save the Word file as “M7-Chall-<your initials>”.
Activity #1 (4 points)
Directions: Using the data provided below as your records (5 records), create an Access Database table using the fields UNIT 1, UNIT 2, UNIT 3 and UNIT 4. Identify the appropriate filing units and enter the data into the database table, ensuring proper formatting (remember all caps and no punctuation) (2 points). Sort the database table by the appropriate fields and screen capture the result of the sort (2 points).
Reference Module 6 for specifics on how to screen capture as well as the overview of ARMA filing rules.
Here are the five records for Activity 1:
Sgt. Pepper Clothing Accessories
The Paris Daily News
6th Avenue Apts
Sgt. William Trundle
#1 Auto Repair Shop
Note. Your screen capture should:
· Show your table in datasheet view with four fields (UNIT 1, UNIT 2, etc.)
· Show all data (no truncations)
· Include the above records broken into the appropriate filing units (refer to the ARMA rules for directions on how to treat numbers (e.g., 6th), symbols (e.g., #), etc.
· And, be sorted according to ARMA filing rules (primary sort by UNIT 1, secondary sort by UNIT 2, etc.)
Activities 2 and 3 are on the next two pages
Activity #2 (4 points)
Directions: Create a database table with appropriate fields (smallest logical units) to store the following customer data (take note that you should use your name for the first customer). Your customer’s names and addresses are listed below:
(Your Name) Ms. April Spring
8405 Jelson Blvd. Taylor Homes, Inc.
Edford, IN 46055 100 West 5th
Carmel, NY 50566
555-555-5555
A2-Step #1 (2 points)
1) Set up your customer’s names and addresses in an Access database table, include a primary key and use appropriate field names. Screen capture each of the following - Remember to clearly label each part:
a) Open you table in design view, place your name in the description section for the “Title” field.
b) Screen capture your table in design view; paste it into your Word document.
c) Screen capture your table with the data entered in it in datasheet view (all data visible).
d) Create a form based on the table. Screen capture the form showing all records on one form page (hint: “continuous form”).
A2-Step #2 (2 points)
2) Create a properly formatted mailing labels report based on Avery 5160 Labels.
a) Screen capture your report for the labels and paste it into your Word document.
Activity #3 (26 points)
Access the “Challenge Problem #1 Data” from the link provided on the website and save it to a location where you can work with it.
A3-Step #1 (2 points)
· Add a record to the “Firm” table. Use 9999 for the FirmNumber, 419/519 for the FirmName and use your name in the contact field. You can leave the other two fields empty.
· Sort the Firm Table in descending order by the Firm Name.
o Screen capture your results and paste them into your word document. You will likely need a few screen captures to show all the records.
§ Take you time and remember to keep things clearly labeled.
A3-Step #2 (2 points)
Screen capture a datasheet that has all the data from the Payments table and is sorted in ascending order first by “Deposit” then by “Amount Paid.” The main sort should be by “Deposit” with the secondary sort by “Amount Paid.” Complete this as one operation (hint – think query).
· Screen capture your results and paste them into your word document. You will likely need a few screen captures to show all the records.
A3-Step #3 (3 points)
Use “Filter by Form” to determine all payments received before 06/04/01 and that are greater then $2000.
· Screen capture your “Filter by Form” screen and paste it into your word document.
· Screen capture your filtered results and paste them into your word document.
A3-Step #4 (2 points)
Query the Payments table for payments made by Firm #1106
· Screen capture your “Query by Example” screen (this is the query in design view) and paste it into your word document.
· Screen capture your query result (this will be a datasheet view) and paste it into your word document.
A3-Step #5 (5 points)
Create a Query to determine the average payment and the total number of payments for Firm #1106. Rename the average field to “Average_Payment” and the total number of payments field to “Number_of_Payments.” Name the Query “Firm #1106 Information.”
· Screen capture your “Query by Example” screen
o make sure all equations are completely visible, and paste it into your word document.
· Screen capture your query results and paste it into your word document.
A3-Step #6 (5 points)
Create a Query to determine the average payment per week. Rename the weekly average field to “Weekly_Average.” Name the Query “Weekly Average Payment.”
· Screen capture your “Query by Example” screen, making sure all equations are completely visible, and paste it into your word document.
· Screen capture your query results and paste it into your word document.
A3-Step #7 (5 points)
Create a Form based on the “Weekly Average Payment” query. Within this form, create a calculated Field that multiplies the “Weekly Average” for each week by 52, set the format of the field to currency and place it to the immediate right of the Weekly Average field. Name your calculated Field “Predicted Annual Revenue.” Place your name in the Form Footer and name the form “Predicted Annual Revenue”.
· Screen capture your form in design view
o Make sure the properties box is open for the “Predicted Annual Revenue” field and that the formula you created is entirely visible.
o Paste this screen capture into your Word document.
· Screen capture your form in Form View and paste it into your Word document.
A3-Step #8 (2 points)
Create a “One-to-Many” relationship between the Firm and Payment tables. Enforce Referential Integrity.
· Screen capture your relationship report and paste it into your Word document.
You have just created a very lengthy Word document, please be sure each part is clearly labeled and in the same order as presented. And, don’t forget to place your name on all of the work.
Congratulations, you’re done!!! Please submit the completed Challenge problem to your instructor.
Page 2 of 3 419/519 Challenge Problem #1