Re: Urgent Help Required
From: John Viescas (JohnV_at_nomail.please)
Date: 03/22/04
- Next message: Perico: "Re: Rounding problem"
- Previous message: Culbert: "Calculated query from a calculated query"
- In reply to: Vasant: "Re: Urgent Help Required"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 22 Mar 2004 15:59:01 -0600
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) "Vasant" <anonymous@discussions.microsoft.com> wrote in message news:eb0301c4104e$00799cc0$a601280a@phx.gbl... > 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 > > >-----Original Message----- > >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" <anonymous@discussions.microsoft.com> wrote in > message > >news:cee901c41047$d9703b30$a001280a@phx.gbl... > >> 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 > >> > >> > > > > > >. > >
- Next message: Perico: "Re: Rounding problem"
- Previous message: Culbert: "Calculated query from a calculated query"
- In reply to: Vasant: "Re: Urgent Help Required"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
