Urgent Help Required

[Vasant]

Hi,

I need urgent help on following queries

Database design
===============
Table Name: Hotel
Fields: HotelNo,HName,City
Primary Key: HotelNo

Table Name: Room
Fields: RoomNo,HotelNo,Type,Price
Primary Key: RoomNo,HotelNo

Table Name: Booking
Fields: HotelNo,GuestNo,DateFrom,DateTo,RoomNo
Primary Key: HotelNo,GuestNo,DateFrom

Table Name: Guest
Fields: GuestNo,GuestName,GuestAddress
Primary Key: GuestNo

I want to write following queries in MS Access 2000 using
SQL query only

1)What is the average no. of bookings for each hotel in
London

2) What is the most commonly booked room type for each
hotel in London

3) What is the lost income from unoccupied rooms at each
hotel today.

Kindly provide me only SQL Queries. I have a limitation
and do not want to use Macros, etc..

Thanks

 

[John Viescas]

Hmmmm. This sounds awfully like a homework assignment.

The first question is ambiguous. Average number of bookings per hotel in
London per what? Day? Week? Month?

For the second problem, you need tables Room and Booking, count by Type, and
sort the count descending.

For the third problem, you need to work with the Room table and use NOT IN
with a subquery on the Booking table to find out which rooms are not
occupied "today" (use the Date() function). Keep in mind that DateFrom is
this sort of database indicates the checkout date. The hotel considers the
room "not occupied" on this date unless another booking shows someone
checking in on the same day. Sum the price of the rooms found.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Vasant]

Hi tahnks for the response, but franly speaking this has
not really provided me a solution

1)The average is per day

2)Counting by Type and using table Room and Booking would
only give bookings and not most commonly booked room type

Would appreciate if you can kindly provide me the exact
query for each of the 3 cases.

Thanks

 

[John Viescas]

Well, you haven't admitted whether this is for a homework assignment or not.
If it's a real business problem, I'll be happy to help you work through the
SQL. The fact that you listed three specific problems - problems that I
might write if I were trying to teach aggregate queries - leads me to
believe this is a homework or test assignment. If it's homework, I'm not
doing you any favors giving you the exact answer. You'll find that most (if
not all) MVPs will refuse to give specific answers to questions that are a
class assignment.

Having said all that, here's how to approach the three problems:

1) To do this correctly, you need a "driver" table containing one row for
each date across the time span that you want to analyze. You need this to
be able to "explode" each booking into one row per day so that you can count
and average them. The SQL will look something like:

qryExplodeBookings:
SELECT Driver.DateField, Booking.HotelNo, Booking.RoomNo
FROM Driver, Booking
WHERE Driver.DateField >= Booking.DateFrom And Driver.DateField <
Booking.DateTo

However, if all you want is average per day based on the "From" or check-in
date, you first need to create a query to Count the bookings per day for
hotels in London and then write a query that uses that as input to get the
Average per hotel. If this must be a single SQL statement, you can "nest"
the Count query inside the FROM clause.

2) You are correct - sorting will get you the most commonly booked room type
overall, but not by hotel. You still need to start from a base of:

SELECT Hotel.HName, Room.Type, Count(Booking.HotelNo) As HotelTypeCount
FROM (Hotel INNER JOIN Room
ON Hotel.HotelNo = Room.HotelNo)
INNER JOIN Booking
ON Room.HotelNo = Booking.HotelNo AND Toom.RoomNo = Booking.RoomNo
GROUP BY HName, Type;

Now build a query on that to select the row for each hotel that is also the
MAX HotelTypeCount for that hotel.

3) First, find the "occupied" rooms today:
SELECT Booking.HotelNo, Booking.RoomNo
FROM Booking
WHERE Booking.DateFrom <= Date() AND Booking.DateTo > Date()

Now, build another query that outer joins Room with this query to find out
which ones are not booked, and then Sum the Price.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)