Reservations form (spreadsheet style)

[BradCollins]

Hi all,

I amtrying to create a form in a spreadsheet style that displays room bookings over a period of time.
Now I have read countless topics on this forum and have managed to create the form (based on the room table) as a continuous form so that each room is displayed in the left column.
I then created a range of unbound fields across the top of the form to contain the date and some navigation buttons to enable the user to scroll through the dates.

I have another raft of unbound fields next to each room on the continuous form that compare the room and the date field and then display wether or not it is booked.

So what I have now looks like this :

Date1 Date2 Date3 Date4 Date5 Date6 Date7
Room1 booked booked
Room1 booked booked
Room1 booked booked
Room2 booked booked
Room2 booked booked


what I would like is the form to look like this:

Date1 Date2 Date3 Date4 Date5 Date6 Date7
Room1 booked booked booked booked booked booked
Room2 booked booked booked booked


Can any assist with the code or design on how to get the room bookings to all appear on only one line.

Thanks in advance for your help and advice

 

[ZmrAbdulla]

a CrossTab query?

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[BradCollins]

consider that I will never know the dates where the room is booked and neither will i know the rooms (or amount of rooms) how would a cross tab query help ?

I must admit that I dont understand them that well. But I did try using one. Maybe I am just doing it wrong, but for the calculatiosn to work in the unbound fields I would need the crosstab query to return data like this :

In Out In Out In Out
Room1 date1 date2 date3 date4 date5 date6
Room2 date1 date2 date3 date4 date5 date6

 

[ZmrAbdulla]

I think you need to have a Calendar type form that will display

Rooms/Date	1/1	1/2	1/3	1/4 
===========================================
Room 1		y	n	n	y

Room 2		y	y	y	y	

Room 3		n	n	n	n	

Room 4		y	y	n	n

There should be a sample available in this forums. "BillPower" was having a sample in his site. Not sure still it available. I can't find the link

http://www.tek-tips.com/userthreadparticipate.cfm?handle=BillPower

Also check here

http://www22.brinkster.com/accessory/forms/005.shtml

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[ZmrAbdulla]

The site of BillPower that is not working now is

http://www.wpower.fsbusiness.co.uk/

as per the reference in

http://www.tek-tips.com/viewthread.cfm?qid=579125

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[MajP]

http://www.4shared.com/dir/6086214/77242a92/sharing.html

Here is one I built that may give you some ideas. It is actually pretty flexible. You can define your own rooms and periods.

This download site is not so user friendly so read the directions carefully.

I use an idea of storing normalized appointment data, but write to a table for display purposes. The display table is not normalized, but it is only temporary for display purposes. This then allows you to use a continous bound form. I think the idea works well.

 

[BradCollins]

MajP

Thank you very much, i have manage to download your Db but as I look at it I cannot see how I can utilise what you have done.
I suspect that its just my inability to understand that is holding me back, so I will continue to look at it.

What you have done is fantastic, but you appear to need to have upto 30 precoded room times. In my Db I dont have that as I am booking rooms for a whole day so the booking data is just a room number, the person who has booked it, the start date and finish date.

But I will continue to review your Db some more as it certainly looks like I could somehow get it to work for me, or at least it may steer me in the right direction.

Thank You

 

[MajP]

I did not read your original post well enough. I think I could modify it quickly to better fit your solution. Your problem is actually much simpler. How many days do you need to display at a time?

 

[BradCollins]

MajP,

I weas hoping to have about 1 month (30 days) across the top however I can live with only 2 or 3 weeks (14 - 21 days)if that is easier.

Thanks again

 

[BradCollins]

MajP,

I was hoping to have about 1 month (30 days) across the top however I can live with only 2 or 3 weeks (14 - 21 days)if that is easier.

Thanks again