Designing a Booking System

[Guest]

Hi, i'm trying to create a booking system for a ski school.
There are about ten instructors, each of which can be booked for either a
morning, afternoon or whole day, on any day of the week. This means that at
most there could be 4000 different bookings.

I already have a table for instructor information and a table for client
information, but am unsure how to store the dates for each booking. Should I
have a table for each instructor with a field for every date?

Hope someone can help.

 

[Nikos Yannacopoulos]

Olly,

Separate tables for similar data are a recipe for disaster. You need ONE
booking table, with fields (at least):

BookingID (autonumber)
Instr_ID
Client_ID
BookingDate
DayPart

Note: you could work with either two or three possible values in DayPart, AM
/ PM or AM / PM / WD (for whole day). In the former case, a whole day
booking would create two records. Personally, I would opt for this approach.

If you make a composite primary key including fields Instr_ID, BookingDate
and DayPart you will avoid double bookings without the need for code to do
it for you (only works with the first option above, i.e. two records for
whole day).

HTH,
Nikos

 

[Guest]

Thanks, that helps.

I've been trying to take the information in this table and produce a query
that contains all possible dates, day part and a field for each instructor
containing the relevant booking reference.

My aim is to then create a from from this query looking a bit like this.

Date Part Ins1 Ins2 Ins3 Ins4 Ins4
Ins5
1/1/2005 am 0 0 2 0 4
0
1/1/2005 pm 0 3 2 0 1
0
1/1/2005 am 5 0 0 0 0
0

etc.

Is this possible, and i can't seem to work out how to do it?

Thanks again.

Olly Bowman

 

[Nikos Yannacopoulos]

Olly,

What are the numbers in the matrix? Client_ID's? Anyway, yes, it is
possible. What you need is a crosstab query. Start by making a simple select
query with all the required fields (Instr_ID, Client_ID, BookingDate,
DayPart) and then go Quriy > Crosstab to change it to a crosstab query. In
the Crosstab line in the grid, set BookingDate and DayPart to Row Heading,
Instr_ID to Column Heading and Client_ID to Value. The Total setting should
be Group By for all but the Value column, which should be set to
First/Last/Min/Max/Sum depending on what you want (if there's only one row
for each day/part/instructor combination in the original table, like it
should be, then it doesn't make any difference which one you use).

The tricky part is to force it to display all possible dates/dayparts in a
range. As it is, if no record exists for a date/part it will not be
displayed at all. One way around it that I can think of is to have a
separate table with all the dates/parts in the period in question and
combine that with the bookings table in the query with a right join so that
it returns all records from the auxiliary table, and the matching records
from the bookings table. If your application involves a form on which the
user selects a date range to display, then you could use some simple VB code
behind the "Show Bookings" button to clear and populate the auxiliary table
for the required date range.
Assuming the table is called tblAux, with fields fDate and fPart (make the
table manually), the form is called MyForm, the two text boxes for the date
range are called txtFrom and txtTo, and the command button is called
Command0, your code would look something like:

Private Sub Command0_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset

strSQL = "DELETE * FROM tblAux"
Set db = CurrentDb
db.Execute strSQL
dfm = Forms![MyForm]![txtFrom]
dto = Forms![MyForm]![txtTo]
Set rst = db.OpenRecordset("tblAux")
vdat = dfm
Do Until vdat > dto
rst.AddNew
rst.Fields(0) = vdat
rst.Fields(1) = "AM"
rst.Update
rst.AddNew
rst.Fields(0) = vdat
rst.Fields(1) = "PM"
rst.Update
vdat = vdat + 1
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
DoCmd.OpenQuery "QueryName", acViewNormal
End Sub

Just change the names to the actual ones and you're there.

HTH,
Nikos