If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Designing a Booking System
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. |
#2
|
|||
|
|||
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 "Olly Bowman" wrote in message ... 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. |
#3
|
|||
|
|||
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" wrote: 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 "Olly Bowman" wrote in message ... 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. |
#4
|
|||
|
|||
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 "Olly Bowman" wrote in message ... 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" wrote: 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 "Olly Bowman" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Office XP | Ed Lester | Setup, Installing & Configuration | 1 | May 27th, 2004 09:30 AM |
Resent system download update problems | Anthony | General Discussions | 1 | May 10th, 2004 02:17 PM |
Direct Booking | Esmond Koo | Calendar | 0 | May 7th, 2004 03:19 AM |
Table design for a booking system | Brian C | Database Design | 2 | April 27th, 2004 03:11 AM |