help with Dates between Startdate and EndDate (1 Viewer)

[subiaco]

I have a student creating a hotel booking database. Tables include: tblGUEST, tblROOM, tblBOOKING

The BOOKING table links Guest with ROOM and has two fields StartDate and EndDate

This is an AS level project so don't want to get into an additional Items table for BOOKINGS

However, I would like them to create a crosstab query for showing room availablity. I've worked out how to do this but it requires a table containing each date a room is booked.

MY PROBLEM....is there a way of appending records to an extra table based upon a record of date and room for each date between the StartDate and EndDate in the tblBOOKINGS table. I realise that once this is done the tblBOOKINGS table will need to be updated in some way so that the process is not repeated

|Preferably via queries rather than code

Just being to list all the dates between the StartDate and End date would also be useful.

Thanks for any help

Kevin

 

[Fizzio]

Your main problem is that you are only storing 2 dates, start date and end date. In order to effectively query each date, as you rightly say, the booking needs to have explicit dates, not a date range to determine if the room is available or not. I'm not sure if this is possible via queries alone as you need to create a date list for every booking before being able to run the crosstab query. I would use VB to create this list and probably use a multidimensional array but you wish to avoid this. I do have a 'timeline' demo that Rich created somewhere so I'll try to dig it out and post / send it.

Another option is to create the date list (again via code mind you) to populate the bookings table. you may have to play around with the data entry form but this is probably the mose effective solution. Essentially you would:
a) Enter a start and end date in the form
b) Open a recordset based on the booking table
c) check if that combo of date / room already exists and if not;
c) Add a date and roomID, GuestID etc
d) loop until the end date is reached.

you then have a comprehensive bookings table with all dates in to interrogate in your crosstab query.

Hope that is helpful.