Double booking preventation

[ICTkirsten]

Hello

I am stressing out over trying to prevent doublebookings on my database which ive spent 2 days trying to figure out

so what ive tried to do is : use queries that will take one away from the other so that when i go onto my form which creates appointments it will do this:

Select Dentist ( via userID) and select the date after this the time field which is a combo box will only display the times that are available and will remove the times take.

Its been a really hard task for me and i am left with lots of queries due to trial and error but ive decided to turn for help as it is taking far too long that it should be

i have attached my queries to show what i have attempted. i would be greatful for any help


thank you

 

[ICTkirsten]

final print shot which shows the time combo box record source

 

[JHB]

I think instead of showing a bundle of queries, it would be much better to show some sample data and also the result you want, then we maybe could give solution to a query!
Remember we do not know how your data in the tables looks like, so how should we be able to determine if your queries is okay.

 

[ICTkirsten]

Hiya,

I just want to be able to have a form that creates an appointment and when choosing the time it will only display times available for the dentist for a particular date. so in other word the times that are not available will be removed from the list of times ( displayed as combo box)... which is meant to be done by doing 1 query minus the other...


I Dont knw how to show sections of my db? im also worried with putting it up as it is for a school project that im not allowed to post up

 

[E.Rowlands]

I'm a relative beginner in Access and studying an A level course like yourself. To solve the double bookings problem on my Hair Salon database (tutor defined the project, not me), I simply created what I understand to be a composite key and added to a new index in my appointments table the:

StylistID
AppointmentDate
AppointmentTine

Sure it isn't a fancy combo box that excludes booked appointments but It has worked rather well. To get past the awful Access error messages about a duplication in the index, IE, when a stylist has an appointment on the same date and time, I've added an error handling macro to display a message box that informs user of duplication. I have concluded that this is the only likely error to occur in my simple database appointments booking function so I am confident that this will not show on other errors (99% sure, after lots of testing)

 

[E.Rowlands]

ICTKirsten watch this simple tutorial on YouTube on how to create a composite key/unique index for the prevention of double bookings.

I personally didn't add the fields to my primary key index but made one called NoDuplicates.

youtube.com/watch?v=uKwLIvV_S9Q[/url] - Please copy and paste into browser, I am not a high enough forum level to post links. Sorry mods for breaking this rule.

 

[JHB]

I just want to be able to have a form that creates an appointment and when choosing the time it will only display times available for the dentist for a particular date. so in other word the times that are not available will be removed from the list of times ( displayed as combo box)... which is meant to be done by doing 1 query minus the other...
..

I've done a small example in the attached database how I would do it, maybe you could get some ideas from that.