Preventing double bookings via a query (1 Viewer)

qwerty55 · Jan 17, 2015

Hi everybody - I am new to this forum (joined right now after spending the last 4 hours trying to prevent double bookings)

So an insight into my database:

It is for a tutoring centre to create a timetable, invoices and register new students.

I have a lesson table which includes the following:

LessonID
Date of Lesson
Time of Lesson
Teacher
Student ID
Price
Paid
Subject

(also have a Parent, student, subject, teacher table)

- I realise I made a big big mistake by putting spaces in my field names, but unfortunately when I try changing that it messes everything else up (my forms etc, I have finished making my entire database, just have to prevent double bookings now).

My teacher isn't very good and unaware of how to do this and told me to research it. I have spent ages trying to do it and have gotten nowhere.

I want to prevent double bookings so that: Teachers cannot be booked for a different subject at the same time and date
and: students cannot be booked for a different subject and the same time and date

If not this, I want the query to show all the data where double booking has occurred.

Any help?

smig · Jan 17, 2015

Your table structure is wrong.
StudentID and Paid should not be in that table.

You should have a manyToMany table:
LessonID
StudentID
Paid

Put LessionID and StudentID as the PK of the table to prevent double booking.

qwerty55 · Jan 17, 2015

smig said:
Your table structure is wrong.
StudentID and Paid should not be in that table.

You should have a manyToMany table:
LessonID
StudentID
Paid

Put LessionID and StudentID as the PK of the table to prevent double booking.

Thank you for your reply! Ahhh, I wish I had picked up on that earlier.
The only problem is, is that if I move where the studentID and Paid bit is, it will mess up my reports, queries and forms. (A long with the written bit of my coursework where I have written what info goes into what table)

Is it at all possible for me to prevent double booking with how my table currently is?

plog · Jan 17, 2015

I disagree with smig in that your table structure is wrong. I don't see what that new table would do.

However, I do see one issue--Date of Lesson and Time of Lesson should be one field. It's called a Date/Time datatype for a reason--it can hold both pieces of data at once. That will save you some headaches down the line.

As for preventing double bookings, I believe you are going to need to use an unbound form. Instead of having your form's record source be the Lesson table, you leave the record source blank and put a 'Submit' button on it. The user completes the form, then clicks 'Submit', it looks into Lessons to make sure it won't create a double booking--if it would it shows a message box informing the user and telling them to correct it. If it doesn't create a double booking, it runs an APPEND query to take the form data and move it to the Lesson table.

jdraw · Jan 17, 2015

Not really and not for the longer term. You can apply some workarounds etc, but I'd like to see the requirements and match them to the structure you have. The critical part of database is to ensure (design and test) that your tables and relationships match your business requirements. Too often, we see posters like you who are "too far into their system" that they don't have time to correct basic design issues. Believe me, life will be much easier if you design your tables--make a data model/erd and test it with test data and test scenarios, before getting involved with Access or any DBMS.

Here's a tutorial that goes through the basic design concepts for tables, and relationships, normalization etc. Work through it, then apply what you've learned to your own database. It will save you hours of frustration.

You can prevent such duplication with proper table, key and index design.
Do NOT use field or object names with embedded spaces--another source for frustration.

Good luck.

qwerty55 · Jan 17, 2015

plog said:
I disagree with smig in that your table structure is wrong. I don't see what that new table would do.

However, I do see one issue--Date of Lesson and Time of Lesson should be one field. It's called a Date/Time datatype for a reason--it can hold both pieces of data at once. That will save you some headaches down the line.

As for preventing double bookings, I believe you are going to need to use an unbound form. Instead of having your form's record source be the Lesson table, you leave the record source blank and put a 'Submit' button on it. The user completes the form, then clicks 'Submit', it looks into Lessons to make sure it won't create a double booking--if it would it shows a message box informing the user and telling them to correct it. If it doesn't create a double booking, it runs an APPEND query to take the form data and move it to the Lesson table.

Thank you for replying.
Unfortunately, I wasn't aware of that - I now realise how silly it was of me to have put it separately.

Really sorry I don't understand what you mean exactly. By record source do you mean the primary key? I have a "save record" button on my form so it would be great if I can get this to work.

Please can you expand on how to change the record source and run an append query?

qwerty55 · Jan 17, 2015

jdraw said:
Not really and not for the longer term. You can apply some workarounds etc, but I'd like to see the requirements and match them to the structure you have. The critical part of database is to ensure (design and test) that your tables and relationships match your business requirements. Too often, we see posters like you who are "too far into their system" that they don't have time to correct basic design issues. Believe me, life will be much easier if you design your tables--make a data model/erd and test it with test data and test scenarios, before getting involved with Access or any DBMS.

Here's a etc. Work through it, then apply what you've learned to your own database. It will save you hours of frustration.

You can prevent such duplication with proper table, key and index design.
Do NOT use field or object names with embedded spaces--another source for frustration.

Good luck.

Just to clarify, since I have spaces does that mean I need to re do the whole database?
Oh dear

I am literally finished and only have the double bookings left. Thanks, I'll have a look at the link!

jdraw · Jan 17, 2015

Can you make a copy of your database; then ZIP it and post. Remove anything confidential/private or "make dummy names for students and teachers"
eg
Joe Blow, Donald Duck, Kody AkBear, Phille T. Fishe.....

It would be helpful to readers if you could post the business rules or specifications for your database.

NOTE: Just saw your comment re spaces ---No it doesn't mean you have to redo everything. It just means you have to enclose such field names within square brackets "[field name with space]" for Access to recognize them.

qwerty55 · Jan 17, 2015

jdraw said:
Can you make a copy of your database; then ZIP it and post. Remove anything confidential/private or "make dummy names for students and teachers"
eg
Joe Blow, Donald Duck, Kody AkBear, Phille T. Fishe.....

It would be helpful to readers if you could post the business rules or specifications for your database.

NOTE: Just saw your comment re spaces ---No it doesn't mean you have to redo everything. It just means you have to enclose such field names within square brackets "[field name with space]" for Access to recognize them.

Attached it here.
I have deleted all of the personal details and reports/forms/queries except for the lesson form. Hopefully that shouldn't be a problem.

The database needs to register students into the organisation. When registering, the choose which subject they want to study. From this, the timetable for each individual tutor is created (already done that). However, it should also be able to prevent double bookings.

That's great, i'll remember next time to have no spaces.
Thanks.

jdraw · Jan 17, 2015

Some additional questions:

What does this mean? EnglishASAQA
In database you try to use atomic fields --1 fact, 1 field. Makes queries and retrieval much simpler and that's what the dbms expects.

What is a teacher compared with a Tutor?
You use both terms and neither is defined.

In tblLesson, why not use the Teacher/tutorID?

Only studentID 2 and 7 have 2 lessons, all others have only 1---- not very realistic.

To prevent duplication in a particular Lesson, you have to ensure that
--date of lesson
--time of lesson
--teacher(ID) ---I don't know why you used Teacher Name here and not ID????
--studentID

all these fields are used to make up a unique composite index. Then the database software will prevent duplicates.

I would recommend against the use of multi-value fields and the use of Lookups at the table field level.

I do not understand the use of tblStudent_1, tblTutor_1 or tblSubject_1 in your relationships.

You still have not provided business rules.

Hope this is useful.

smig · Jan 17, 2015

plog said:
I disagree with smig in that your table structure is wrong. I don't see what that new table would do.

However, I do see one issue--Date of Lesson and Time of Lesson should be one field. It's called a Date/Time datatype for a reason--it can hold both pieces of data at once. That will save you some headaches down the line.

As for preventing double bookings, I believe you are going to need to use an unbound form. Instead of having your form's record source be the Lesson table, you leave the record source blank and put a 'Submit' button on it. The user completes the form, then clicks 'Submit', it looks into Lessons to make sure it won't create a double booking--if it would it shows a message box informing the user and telling them to correct it. If it doesn't create a double booking, it runs an APPEND query to take the form data and move it to the Lesson table.

How should he put many students into this table ?

Should he put all lession details for every student take this course ? If so, what lessionID is for ?

I think you should rethink your structure from scratch.

jdraw · Jan 17, 2015

I took a quick look at structure based on comments and came up with the attached as a starting draft. It can be fleshed out/modified as details are received.

I don't know if this is a typical school class with scheduled classes, or a particular tutor gives a lesson. And the OP hasn't been clear.

Preventing double bookings via a query (1 Viewer)

qwerty55

New member

smig

Registered User.

qwerty55

New member

plog

Banishment Pending

jdraw

Super Moderator

qwerty55

New member

qwerty55

New member

jdraw

Super Moderator

qwerty55

New member

jdraw

Super Moderator

smig

Registered User.

jdraw

Super Moderator

Attachments

Similar threads

Users who are viewing this thread