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.
PREPARATION
· 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 necessary.
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 permanent accommodations.
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 could.
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.
Thank you.
Yours truly.
Amy Patterson
CustomersCustomer IDLast NameFirst NamePermanent AddressCityStateZipPermanent TelephoneAngerstein10001
Amy
214 Oak Ave
Radnor
PA
(215)990-000919080
Babbles10002
Bob
567 5th St
Carlisle
MT
(516)990-888959428
Codfish10003
Colburn
3 Ivy Lane
Radnor
PA
(215)666-777619080
Dellabella10004
Debra
7890 Pike St
Newark
DE
(201)445-675419711
Edelweiss10005
Edna
819 River Rd
Mola
LA
(223)009-123470113
Fantail10006
Frederick
9 Kiddle Rd
Cutbank
MT
(899)887-654559427
Your Name10007
Your Name
Your Address
(000)000-0000
RentalsCustomer IDRoom IDDate In10001Date Out
1
9/1/1999100029/10/1999
5
9/12/1999100039/30/1999
6
9/5/1999100049/6/1999
3
9/6/1999100059/12/1999
4
9/4/1999100069/30/1999
7
9/15/1999100079/30/1999
2
9/1/19999/10/1999
Room ChargesRoom TypeDouble w/ BathPrice per Night
Double w/o Bath$30.00
Single w/ Bath$20.00
Single w/o Bath$20.00
$10.00
RoomsRoom IDRoom Type1Location
Double w/o Bath
1st floor
2
Double w/o Bath
1st floor
3
Single w/o Bath
1st floor
4
Single w/ Bath
1st floor
5
Double w/ Bath
2nd floor
6
Single w/ Bath
2nd floor
7
Double w/o Bath
3rd floor
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
Fig. 1-5
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 button—Properties—Format—Currency.
· Preview the report to make sure it will print correctly. Your report should resemble the one that follows.
September Bills
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