Designing a booking system - Day/Time slot/Details 1

[scliffe]

Hi,

I've designed the table and query structure of a database to book people into 1 of 3 different types of fitness suite programme. My initial plan was to select the date on the form (using a pop-up calendar) and this would then display the day's bookings (in a sub-form?) for each of the 15 minute slots through the day, simply displaying the type of programme. Clicking on one of these would make visible a (2nd?) subform displaying the full information.

I've managed the calendar pop-up, designed the (2nd) sub form and linked the forms on ID, but I'm unsure as to the best way to approach the list of timeslots in the day. Could if be made into (dynamic) buttons that change the displayed letter (N, I or P) according to the Programme type of the booking?

I'll happily provide an additional info I can to help. Any pointers would be most appreciated.

Thanks in advance

Simon

 

[MichaelRed]

ref: thread702-1341418




MichaelRed

 

[scliffe]

Thanks for the suggestion MichaelRed, but as regards a shrink-wrapped option... I work for a 6th form college and we're currently writing our own in-house software to replace other systems and reduce costs. This is a new system that I only get chance to work on around other projects. Maybe my original post over-simplified. I would never to claim to be a great programmer but I found your response a little short. Maybe it will be too difficult, but I relish a challenge (plus I can call on additional support within the team).

All I'm after is the best direction to go down as I'll happily do the donkey work and only return for an occasional question no-one else on the team can answer - I don't expect other people to actually DO the job for me!

Sorry if this sounded like a mini-rant (its not meant to be)

 

[MajP]

Simon,
First take a look through this search feature on this site. There has been a lot written on "booking systems" in the past. I kind of agree with Michael in that this sounds like something that is ideal for Outlook, and usually if people have Access they also have Outlook. Using custom forms and VBA in Outlook you can have basically what appears to be a stand alone utility.
However if you want to build this there are a couple of approaches. One way if you have a few time slots you can use an unbound form. In your case this may be a pain to do (15 min blocks for 10 hours means a minimum of 40 controls). However the relationships are very simple, just a lot of "donkey" work as you call it.

The other way is to make a booking Table

tblBookings
autoBookingID
..other fields
dtmBookingDate
lngPeriodID_FK

Now here is the trick I use. I build a table for display purposes only, this is the recordsource for your subform and shown as a continous form. Each record is a time slot. So if you have 40 time slots you should have 40 premade records with start and stop times.

tblDisplay
autoPeriodID_PK
dtmPeriodStartTime
dtmPeriodStopTime
... other fields grabbed from booking table

The way this works if when you move to a specific date the first thing you do is clear out the data in the tblDisplay for all fields except the period start and stop (you never delete the records). Do this with a sql delete qry. Next you read the booking table and and if there is a record for the given date you write the values you want into the correct fields at the correct time in tblDisplay. I do this with ADO or DAO and loops, but you can use an update query.
Now adding a booking is a two step process. First you click on the time slot which brings up a form whose recordsource is the booking table and set at the correct time period. When you save the record you then have to write to your tblDisplay.
So tblDisplay looks like a grid with the time period start and stops listed down the left side. You never delete those records but just clear data and write data to the other fields (columns) as you switch days or edit the tblBookings.
This may sound confusing because tblDisplay is used in a nontraditional way. You never add or delete records, just clear and push data into certain fields. However, this approach requires a lot less form design and simple relationhips.
However, as Michael hinted this can get real complicated depending on the flexibility you want and Outlook or something else may be the way to go. For example allowing the user to book an event across multiple time periods.

 

[scliffe]

Thanks for the time taken to respond. I realise it could be a very difficult job to do (we have a version of a booking system done VBA/SQL from within another Access database that is functional if not the clearest (I'll not get into the HCI issues here) so I knew something would be feasible, it was just knowing where to start looking. I'll try to remember to report back once I get something substantial done (as mentioned, its around other projects). I guess I'd never considered Outlook as I'd never seen it really done down that route.

I'll look further on "booking systems" its only with most seemingly being so different in design that I've not gotten far with others. I like a challenge (ever since being sat down with Access 2.0 and being asked to rewrite a database with nothing but the (shudder) built-in help and plenty of trial and error).

Thanks again

Simon