Transcript BUS 206 Access Assignment

BUS 2206 Access Assignment
Hints on the DB Assignment
Case 2
The Rent-A-Dress Database
Problem-Solving Cases in Microsoft Access and Excel, 12th Edition, 2015
HF5548.M525 B73 2015
P.57 – P. 62
2015 Term 1
Introduction
• A database for a business that rents designer
dresses
• Please read p.57 and p.62 of the Lab textbook
for background information of the assignment.
Deliverables (1)
• Refer to Lab textbook P.57 – P. 62
1. You need to build 3 database tables and populate
them with data.
2. You need to construct 1 Form, 5 queries, and 2
reports.
• Refer to p.62, items 2, 3, 4, 5, 6, 7, 8, 9, and 10
are needed for the assignment.
• Make sure you understand the material
covered in tutorial A and tutorial B (P.3-P.50)
of the lab textbook in order to work on the
assignment.
Details of the deliverables (2)
Refer to page 62 of the Lab textbook
2. Tables created in Access
3. Form and subform: Customers
4. Query 1: Upper Limit of Price Range
5. Query 2: Dresses Kept Over 7 Days
6. Query 3: Total Dresses Rented
7. Query for Report 1
8. Report 1: Savings by Rental
9. Query for Report 2
10. Report 2: Rental Summary
Database Design
Tables involve:
1. Customers table: data input manually
2. Dresses table: data input manually
3. Rentals tables: data input manually
Customers Table (1)
Customers Data (1)
primary key
Dresses Table (2)
Dresses Data (2)
primary key
Rental Table (3)
Rentals Data (3)
Compound key
Unique
combination of a
compound key
Table Relationships
• Enter 8 customers records.
• Enter 20 dresses records.
• Enter 31 rentals records.
Relationship (2)
Queries (1)
Query 1: A parameter query called “Upper Limit of Price
Range” that prompts for an upper limit to the rental price
of a dress and then displays columns for Dress ID, Designer,
Dress Name, Color, and Rental Price.
Query 2: A query called “Dresses Kept Over 7 Days” that
calculates the total number of days each dress has been
held by a customer and then lists dresses that have been
kept longer than the seven-day limit. The query should
include columns for Last Name, First Name, Dress ID,
Designer, and Total Number of Days Held. The Total
Number of Days Held heading is a column heading change
from the default setting provided by the query designer.
Queries (2)
Query 3: A query called “Total Dresses Rented” that counts
the number of dresses rented by each customer. The query
should include columns for Last Name, First Name, Email
Address, and Number of Dresses Rented. Sort the output
so that the customer who rents the most dresses appears
at the top of the list. Note the column heading change
from the default setting provided by the query designer.
Query for Report 1: It summarizes the amount of money
customers saved by renting designer dresses instead of
buying them. It shows Dress ID, Designer, Dress Name,
Color, Rental Price, Retail Price, and Savings. The Saving
column shows the difference between the rental price and
retail price.
Queries (3)
Query for Report 2: It shows Last Name, First Name,
Designer, Dress Name, and Rental Price.
Form and subform: Customers
Report 1: Savings by Rental
It summarizes the amount of money customers saved by
renting designer dresses instead of buying them. It uses
“Query for Report 1”. A portion of the report appears in
the following figure.
Report 2: Rental Summary
The report uses “Query for Report 2”. The Last Name and
First Name columns are on the same line and all fields are
formatted and visible. A portion of the report appears in
the following figure.
The End
and
Have Fun!