Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2016
    Location
    Northpole
    Posts
    7

    Unanswered: Calculating Ave of Room type in Query using 1 table

    Hi there

    I am trying to calculate average length of stay per room type (Bay-window, Ocean, Side) in Query with all the data in one table. How can I find the average. In Query, I have selected Room, Room Type, Arrival Date & Departure date. Using Builder, what formula and how should I formulate.

    My table includes the following columns:
    ID
    Guest First Name
    Guest Last Name
    Room
    Room Type
    Arrival Date
    Departure Date
    No of Guests
    Daily Rate

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    SELECT AVG ([departure date]-[arrival date]) as StayLength, [room type] FROM [my table]
    GROUP BY [room type]
    ...place that in the SQL view of the query designer

    A general comment column and table names should not use reserved words and or symbols. Because you have used a space (reserved symbol) that means affected columns / tables must be delimited with [ & ]
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2016
    Location
    Northpole
    Posts
    7

    Thank you but it Didn't work

    I tried this exactly, my table = Reservations, and I CAP the data. All in the SQL.

    SELECT AVG ([Departure Date]-[Arrival Date]) as StayLength, [Room Type] FROM [Reservations]
    GROUP BY [Room Type]

    This is what is in my SQL right now:

    SELECT Reservations.[Room Type], Reservations.[Departure Date], Reservations.[Arrival Date]
    FROM Reservations
    ORDER BY Reservations.[Room Type];
    SELECT AVG ([Departure Date]-[Arrival Date]) as StayLength, [Room Type] FROM [Reservations]
    GROUP BY [Room Type]

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...so?
    Thats two separate queries. Its not at all surprising 'it doesnt work'

    How or where are you trying to use this?
    What do you mean by 'CAP the data'

    what happens if you run the query supplied but modified for your table/column names
    Last edited by healdem; 02-11-16 at 06:48.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2016
    Location
    Northpole
    Posts
    7

    It worked

    Thanks, I was working in a practicing query rather than opening a new one.

    Can you help me out again,

    I have to find Base income per room

    I have attached the table picture here.

    Click image for larger version. 

Name:	Crop Reservations.PNG 
Views:	4 
Size:	65.3 KB 
ID:	16769

  6. #6
    Join Date
    Feb 2016
    Location
    Northpole
    Posts
    7

    To add to the last help

    I forgot to add,

    Fifth and sixth guests must pay an additional $20 charge each per day.Guests staying for 7 days or more receive a 10% discount on their daily room rates.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •