Preventing the same combination of data from being entered into a form

[davewong911]

Hi all, I'm doing a college database project for a music lesson company, I've encountered a problem and my teacher cannot help so he suggested that I use a forum.

So in the pictures attached, there is a form called 'Bookings' which the music lesson guys will use to create lesson bookings, the relationships are also in the group of pictures attached. The 'Bookings' form gets a student name from the student table, a teacher name from the teacher table, and a possible lesson time/date from the lesson time table.

The problem is, a teacher cannot be teaching different students at the same time/date. When I create a record and say for example, 5:30, monday with k.breeze, and I create another record, it allows me to input the same time/date and teacher for a booking, which is not right. I need the form to stop the same combination of a teacher name, lesson time and date from being entered.

I need the form to stop me from creating two bookings with the same teacher/time/date so any help will be appreciated. Also, in the pictures attached, there is an available lesson table. This table has every available time that a lesson could possible have, from mon-sun 08:00-20:00. Would this be the best way to organise this information? Or should a combo box be used?

Ideas, solutions and questions are welcome, thanks. Dave

 

[Swemebegur]

Hi davewong911

On the BookingsTBL, put a unique index on the combination of Teacher ID and Lesson ID.
BTW - it is not a good idea to have spaces in field names - you will have to enclose the field names in square bracket all the time.

HTH

Swemebegur

 

[davewong911]

Thanks a lot, I had a look and indexing and that works really well!

 

[Swemebegur]

Hi davewong911

Glad to be of help. In your second question, you write "or use a combo box". I would have thought that this would be the preferred way to display the data on a form. Or are you thinking of a combo box with a field list as its row source? It would be much better to use a table as the row source. I would prefer to use two tables and two combo boxes, one for the day and one for the time. This would make it much easier for entering data. You would need to have separate fields for them on the BookingsTBL, of course. And I would not use surrogate keys on these tables, when you have perfectly good natural keys. But I don't want to stir up that stale old debate

Swemebegur

 

[davewong911]

Yeah I created new fields in the BookingsTBL to allow the user to choose a date of the week and then a lesson time today instead of the previous method. The indexing has fixed the duplication problem and my teacher has learned something new so thank you