"yk" <oy*****@gmail. com> schreef in bericht
news:44******** **@news.tm.net. my...
I am setting up a simple hotel reservation application which have two
dates, i.e. CheckIn and CheckOut. I would like to construct a graph based
on a crosstab query. Therefore, I need to have columns for each date
between CheckIn and CheckOut and the numeric 1 in the field. I have tried
but not successfull. Anyone can help me out? TQ.
Tricky, but possible. I did the same in a reservations-database for a
campsite generating a report to show bookings in a certain month (days
horizontal, fields vertical, bookings as the value) and to show all bookings
(fields horizontal and dates vertical continuing on the next pages)
First: create a table / query with all possible dates you would like in your
output. This is possible by creating a table with numbers from 1 to the
largest possible booking-length you can imagine (+ some extra). Call this
"Days".
Let's assume you have a maximum stay of 5 days, so table Days contains five
rows with values 1, 2, 3, 4 and 5.
Let's further assume you have 1 booking with BookingID = 10, CheckIn =
4-3-2006 and Checkout = 6-3-2006
Second: After that, query your table with reservations and 'days' WITHOUT
joining them. This trick is important.
Query both tables without join would result in all possible combinations,
output fields [BookingID] and [CheckIn]+[Day] results in:
10, 4-3-2006
10, 5-3-2006
10, 6-3-2006
10, 7-3-2006
10, 8-3-2006
Add a criterium om column [CheckIn] + [Day] <= [CheckOut]
This results in
10, 4-3-2006
10, 5-3-2006
10, 6-3-2006
Third: continue to do whatever you want. For instance: query the above
result in combination with RoomNumber by joining this with [Bookings] on
[BookingID] = [BookingID]. Make this your cross-tab query with the room
numbers as column headers and the dates as row headers. Show
Count(BookingID ) as the value, and if you see a value larger than 2, you'd
have found a doubly-booked room. Or join this with the table Customers and
show first([Firstname] & " " & [LastName]) as the value to get a cross tab
overview of all customers for each room and for each date.
Experiment with left-joins to get all rooms included, thus also showing the
rooms that are vacant.
Continue to make it fancy with the cross-tab report wizard and work the code
to show occupied rooms in yellow background. Endless possibilities.
Bas Hartkamp.
