plz help me to solve this.. its very urgent.. i tried a lot but was not able to generate report.... can u guys try to solve
this it would be of great help..n plz tell me d steps... thanks..
SETTING UP A RELATIONAL DATABASE TO
CREATE TABLES AND REPORTS
In this case, you will create a relational database for a room*ing house in a college town. First, you’ll create four
tables and populate them with data. next, you’ll create two reports: one will track the frequency of rentals; the other will
generate occupants’ bills.
· Before attempting this exercise, you should have expe*rience using Microsoft Access.
· Complete any part of the previous Access tutorial your instructor assigns, or refer to the tutorial as
Your instructor has just received the following letter from a former student.
Dear Professor Sanchez,
I have been asked to finish a database project that I began last year. Because I will be traveling so much with my new job,
I won’t have time to complete it. I hope some of your students might be able to help me. Here are the details.
Mrs. Grey owns a rooming house in College Town, Missouri. Students’ visitors often stay at Mrs. Grey’s because it is cheap,
clean, and almost always available. Students attending the university often spend a few weeks at Mrs. Grey’s before finding
The rooming house has seven rooms, some of which are double rooms, i.e., have two twin beds, and some are single rooms. In
addition, some rooms have a bathroom.
When I stayed at Mrs. Grey’s Rooming House, I convinced her that instead of paying my rent, I could develop an Access
database system for her to streamline her operation. I started the project but then was so busy with final exams and job
hunting that I couldn’t finish the database. Mrs. Grey has called me to complete the database system—or else find someone who
The database management system will be used to do the following:
1. Keep track of customers and their permanent address.
2. Keep track of the rooms in the house and their room type.
3. List the price of each room type.
4. Record the length of a stay in a specific room by a customer.
5. Rank the popularity of each room.
6. Generate monthly bills for customers.
I have designed the tables on the next page (in Microsoft Access) to do the first four tasks.
In addition to having these tables, Mrs. Grey requested two reports: the first report should show her how often each type
of room is rented, with the most popular room being noted first, sorted to the least popular. Next, she would like a report
from which she can generate her occupants’ end-of-month bills. This report should include the occupant’s name, address, date
first rented and total amount due.
I hope some of your students can help me with this project.
Customers Customer ID Last Name First Name Permanent Address
City State Zip Permanent Telephone 10001 Angerstein
214 Oak Ave
567 5th St
3 Ivy Lane
7890 Pike St
819 River Rd
9 Kiddle Rd
10007 Your Name
Rentals Customer ID Room ID Date In Date Out
9/1/1999 9/10/1999 10002
9/12/1999 9/30/1999 10003
9/5/1999 9/6/1999 10004
9/6/1999 9/12/1999 10005
9/4/1999 9/30/1999 10006
9/15/1999 9/30/1999 10007
Room Charges Room Type Price per Night Double w/ Bath
$30.00 Double w/o Bath
$20.00 Single w/ Bath
$20.00 Single w/o Bath
Rooms Room ID Room Type Location 1
Double w/o Bath
Double w/o Bath
Single w/o Bath
Single w/ Bath
Double w/ Bath
Single w/ Bath
Double w/o Bath
ASSIGNMENT 1 CREATING TABLES
Use Microsoft Access to create the four tables requested in the letter to Professor Sanchez. Use the data provided in the
letter. Add your name and address to the Customer Table, using a Customer ID of 10007.
‘ASSIGNMENT2 CREATING REPORTS
Assignment 2A: Generating a Report that Ranks Data
Create a report that lists the number of nights each room is rented, listing the most popular room at the top and the least
popular room at the bottom.
• You must create a sigma query and sort the output on the Number of Nights.
• Title the report Number of Nights Rooms Were Rented.
• Headings for the report are as follows:
Room Type Number of Nights
• Make sure you view the Print Preview of the report to make sure that it looks good. Your report should resemble the
one that follows.
Number of Nights Rooms were Rented
Room TypeNumber of Nights
Double w/o Bath33
Single w/ Bath27
Double w/ Bath18
Single w/o Bath6
Assignment 2B: Generating a Billing Report
Generate a report that shows each customer’s bill for September.
You must create a query with a calculated field to figure the charges for each person.
Title the report September Bills.
· Use the following headings in the order given: First Name, Last Name, Permanent Address, Date In, Charges.
· Make sure all fields are formatting properly; for example, check Charges—it should be in currency format. If not,
find the object in Report Design, select it (handles appear around it), click the RIGHT mouse
· Preview the report to make sure it will print correctly. Your report should resemble the one that follows.
First NameLast NamePermanent AddressDate InCharges
BobBabbles567 5th St9/12/1999$540.00
EdnaEdelweiss819 River Rd9/4/1999$520.00
FrederickFantail9 Kiddle Rd9/15/1999$300.00
AmyAngerstein214 Oak Ave9/1/1999$180.00
Your NameYour NameYour Address9/1/1999$180.00
DebraDellabella7890 Pike St9/6/1999$60.00
ColburnCodfish3 Ivy Lane9/5/1999$20.00
(68.5 KB, 2 views)
Reply With Quote
12-17-2011, 07:08 AM
Windows 7 32bit Access 2010 32bit
Join Date Nov 2011
Please tell us your problem in a nutshell - reading through that long requirements list makes it difficult to separate
out what your problem is.