plz help me to solve this.. its very urgent..

[robin]

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

Customers

Customer ID

Last Name

First Name

Permanent Address

City

State

Zip

Permanent Telephone

10001

Angerstein
Amy
214 Oak Ave
Radnor
PA

19080

(215)990-0009

10002

Babbles
Bob
567 5th St
Carlisle
MT

59428

(516)990-8889

10003

Codfish
Colburn
3 Ivy Lane
Radnor
PA

19080

(215)666-7776

10004

Dellabella
Debra
7890 Pike St
Newark
DE

19711

(201)445-6754

10005

Edelweiss
Edna
819 River Rd
Mola
LA

70113

(223)009-1234

10006

Fantail
Frederick
9 Kiddle Rd
Cutbank
MT

59427

(899)887-6545

10007

Your Name
Your Name
Your Address



(000)000-0000

Rentals

Customer ID

Room ID

Date In

Date Out

10001
1

9/1/1999

9/10/1999

10002
5

9/12/1999

9/30/1999

10003
6

9/5/1999

9/6/1999

10004
3

9/6/1999

9/12/1999

10005
4

9/4/1999

9/30/1999

10006
7

9/15/1999

9/30/1999

10007
2

9/1/1999

9/10/1999

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

$10.00

Rooms

Room ID

Room Type

Location

1
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

[aytee111]

Please tell us your problem in a nutshell - reading through that long requirements list makes it difficult to separate out what your problem is.

[robin]

in this i need to create relationship database among 4 tables.. n generate 2 diffnrnt report..
i created the database but not able to generate the report..
can u plz tell me which query to use n how to generate the report..

[robin]

here is the database i created but not able to generate the report plz see....