The situation i have is for my A level coursework i'm trying to create a booking system for a guitar shop that also runs lessons. I have a form set up for booking with Customer, Instructor, Date and Time. What I need is when you click on Time, there is a drop down box with a list of the FREE appointments for the instructor that has been chosen (so only where no previous bookings have been taken for that date and time, these show on the drop down list). I've tried several ways to do this but i'm stumped. What is the best way to achieve this?
Booking form
- Thread starter nodtomc
- Start date
MarkK
bit cruncher
- Local time
- Today, 00:14
- Joined
- Mar 17, 2004
- Messages
- 8,440
Not sure if you have this bit, but I think you're going need some structure that provides rules about when an appointment might occur. For instance, maybe appointments can only start on the hour, and only for certain hours of the day. Then you can compare new data against this model for validation and you can compare existing data against this model to see what doesn't exist for a given day.
well it was suggested to me that i create a table that is essentially a rota system, which shows which instructors have which appointment slots, and then create the queries from that but i'm a little lost. i've got the query which looks up which appointments are booked. i TRIED one which would look up the free appointments, but i only managed to lookup ALL free appointments, and what i need is it to lookup the free appointments of a specific instructor, depending which one has been selected on the form.
toaster045
UK_Student
- Local time
- Today, 08:14
- Joined
- Feb 11, 2008
- Messages
- 39
are A level ICT I know it well (i'm doing it now and this forum is great for the little things we just aren't taught 
).
Ok I'm not exactly sure if this would work but: what if you created a table with simply all the appointments (e.g. 10am - 11am) to make it easier then on in the same table have a tick box so that you can select if that appointment is free or not. Then build a query to filter out all the appointments that are free i.e. those that aren't ticked then create a text box on the form based on the results of that query then that *should* show only the free lesson.
To be honest this may be a shot in the dark and for the technically aspects you might need someone else to step in (that's if this would work).
Ironically i'm doing a hotel booking database and tried to filter out free rooms for my project (like you are lessons) but it got way over my head and i gave up
Hope this helps in some way
Toby
).Ok I'm not exactly sure if this would work but: what if you created a table with simply all the appointments (e.g. 10am - 11am) to make it easier then on in the same table have a tick box so that you can select if that appointment is free or not. Then build a query to filter out all the appointments that are free i.e. those that aren't ticked then create a text box on the form based on the results of that query then that *should* show only the free lesson.
To be honest this may be a shot in the dark and for the technically aspects you might need someone else to step in (that's if this would work).
Ironically i'm doing a hotel booking database and tried to filter out free rooms for my project (like you are lessons) but it got way over my head and i gave up
Hope this helps in some way
Toby
MarkK
bit cruncher
- Local time
- Today, 00:14
- Joined
- Mar 17, 2004
- Messages
- 8,440
Essentially a booking is a discrete thing that describes the relationship between an instructor and a customer. This relationship occurs in time and in space, so there are the data you will need to allocate space for in a booking record. BookingID, CustomerID, InstructorID, Date, Time, Location (maybe a room number or something).
This is a many-to-many relationship since one instructor might teach many customers, and one customer might be taught by many instructors.
Query your booking table to determine what instructors, time slot and rooms are already allocated.
Then you'll need data validation logic. What days are the store open? Do you have an instructor schedule table from which you determine if an instructor is available on a particular day? Do you have a table of available rooms? Is there a finite set of start times? Maybe that's a table, maybe it's calculated, maybe it's open ended. Are appointments fixed in length?
Answering these question will begin the determine what other structures you're going to need.
This is a many-to-many relationship since one instructor might teach many customers, and one customer might be taught by many instructors.
Query your booking table to determine what instructors, time slot and rooms are already allocated.
Then you'll need data validation logic. What days are the store open? Do you have an instructor schedule table from which you determine if an instructor is available on a particular day? Do you have a table of available rooms? Is there a finite set of start times? Maybe that's a table, maybe it's calculated, maybe it's open ended. Are appointments fixed in length?
Answering these question will begin the determine what other structures you're going to need.
