Results 1 to 4 of 4
  1. #1
    robin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    3

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

    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

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Please tell us your problem in a nutshell - reading through that long requirements list makes it difficult to separate out what your problem is.

  3. #3
    robin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    3
    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..

  4. #4
    robin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    3
    here is the database i created but not able to generate the report plz see....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-29-2011, 01:37 AM
  2. please help urgent...thanks
    By dadas in forum Access
    Replies: 4
    Last Post: 10-13-2011, 01:42 PM
  3. Even Microsoft Couldn't Solve This One...Can Anyone?
    By italianfinancier in forum Reports
    Replies: 2
    Last Post: 06-01-2011, 07:21 PM
  4. How to solve this subform issue?
    By Patience in forum Forms
    Replies: 2
    Last Post: 06-28-2010, 07:09 AM
  5. can you solve my problem please?
    By grad2009 in forum Access
    Replies: 2
    Last Post: 02-16-2010, 05:02 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums