Many to many within Many to Many

[Ruegen]

I need some help on some tables -

I've got a booking table - this imports the name of the show using a combo box.

Then I have a schools table with the name of the school.

I need to make bookings for teachers that call in but they could be multiples from the same schools or any, and or join in on the same booking.

Ideally the picture is you create a new booking record, select one or more customers, and in those you type in their name and select the school they are coming from. So you might have 3 people booked in for the one show all from different schools. I can't put their name in the schools table because that keeps all the school details not the teachers details, and there also might be more than one teacher from that school calling in.

I don't know where to go form here. Everything I try doesn't work. I create a subform so I can have many teachers, and within each record I can type their name but I can't add the school. I have a junction between the teachers and the bookings for a many to many relationship and I have even put a many to many junction between the school and the teacher (each teacher is created on the spot at the time of the booking - we don't know who is going to book).

How do I bring across the details into the subform?

Table 1 - Teachers
TeacherName
TeacherSurname

Table 2 - Schools


School ID Number (name in combo box via second column in list view).

Table 3 - Booking
ShowName


So Joe Somebody rings in, from 123 School and gets added to the booking.

Then Sam Blue rings in and wants to be added to the same booking - that person may or may not be the from the same school.

As I don't know how many schools and customers I will have - to as many as I need for each booking.

Also another booking with a different show might go to the same school...

[Ruegen]

Nevermind I was thinking this the wrong way around, I made the teacher the form, made the booking the subform and made the bookings table in that subform...

Just hope it works and I can add more to the booking later...

[June7]

Consider:

tblSchools
SchoolID (primary key)
SchoolName

tblTeachers
TeacherID (primary key
LastName
FirstName
SchoolID (foreign key)

tblShows
ShowID (primary key)
ShowName

tblBookings
ShowID (foreign key)
TeacherID (foreign key)

[Ruegen]

Consider:

tblSchools
SchoolID (primary key)
SchoolName

tblTeachers
TeacherID (primary key
LastName
FirstName
SchoolID (foreign key)

tblShows
ShowID (primary key)
ShowName

tblBookings
ShowID (foreign key)
TeacherID (foreign key)

Would your method allow me to add a school to the teacher rather than the booking? I still want to search schools to see what bookings they have or see which schools are under a booking.

[Ruegen]

I tried your suggestion however for reasons I can't explain I get errors saying I can't make a new booking record because it would create duplicates.

I want to add a new bookings and put in a new teacher with a new school. Because I don't know who the teacher will be yet I have to type it on the booking (however that can't be kept in the booking table).

Why am I now getting duplicate errors? I got the same error when I put a junction between the teacher table and the booking table (I moved the schoolid from the teacher table to the booking table).

I tried making a form of the teacher table, then putting a subform of the booking with no luck.

I tried making a form of the booking table and puttiing a subform of the teachers with also no luck.

All I want is multiple teachers, with a school of their choice on a booking - why isn't that working?

[June7]

I was assuming a teacher could be associated with only one school. However, if you want to allow for historical records that permanently associate a school with booking regardless if teacher changes job to another school, then my suggested structure won't work. Instead, maybe:

tblBookings
ShowID
TeacherID
SchoolID

Bind that table to a form with 2 comboboxes. Set combobox LimitToList property as Yes and use combobox NotInList event to add records to the lookup tables. http://www.blueclaw-db.com/access_no...ed_example.htm

Use form/subform arrangement. http://office.microsoft.com/en-us/ac...010098674.aspx

1. main form bound to tblShows and subform bound to tblBookings with comboboxes for teachers and schools

or

2. main form bound to tblSchools and subform bound to tblBookings with comboboxes for teachers and shows

or

3. main form bound to tblTeachers and subform bound to tblBookings with comboboxes for shows and schools

[Ruegen]

I was assuming a teacher could be associated with only one school. However, if you want to allow for historical records that permanently associate a school with booking regardless if teacher changes job to another school, then my suggested structure won't work. Instead, maybe:

tblBookings
ShowID
TeacherID
SchoolID

Bind that table to a form with 2 comboboxes. Set combobox LimitToList property as Yes and use combobox NotInList event to add records to the lookup tables. http://www.blueclaw-db.com/access_no...ed_example.htm

Use form/subform arrangement. http://office.microsoft.com/en-us/ac...010098674.aspx

1. main form bound to tbShows and subform bound to tblBookings with comboboxes for teachers and schools

or

2. main form bound to tblSchools and subform bound to tblBookings with comboboxes for teachers and shows

or

3. main form bound to tblTeachers and subform bound to tblBookings with comboboxes for shows and schools

I didn't know you couldn't create a new record on the fly with a many to many and that it had to be in place before you could combine it to a booking (to which case there isn't a teacher until the booking is created.

I think with the VBA code it would get around that record and allow me to do that on the fly (assuming the user of my database does add a new record "account") when prompted by the VBA code.

Correct me if I am wrong (I'm half asleep when writing this)..

The problem I see with option 1 is that I will have to have columns for tblBookings for those comboboxes and that will limit the number of teachers and schools I can add to the one booking so I'd be making multiple bookings for the one show - not grouping several schools into one booking of that show.

The problem I see with option 2 is I have more than one school attending a single booking (could be many) and as above limited by the number of columns in the tblbookings.

The problem I see with option 3 is I only get one teacher bound to tbl bookings again limited by the number of columns in tblbookings. I can't add another teacher to the same booking....?

[Ruegen]

I thought maybe this setup if I can create a new record in the junction of either the school or the booking on the fly without having a record in there already...

I was hoping create a form of tblBookings with a subform of teachers that would allow me to:
1. Make a new booking record using the form.
2. Add in the subform, 1,2,3, and so on teachers via a continuous form.
3. Add the school to to each of those teacher records.

then

1. Create a new booking with totally new teachers and schools. and have each booking show the schools later to see what schools have booked what show.

[June7]

Your understanding of the form/subform arrangement seems flawed. There is only one field for the TeacherID in tblBookings. There will be multiple records for each ShowID. The combination of ShowID, TeacherID, SchoolID should be unique for each record in tblBookings.

Regardless of which form arrangement used (can incorporate all 3 in the db), the table structure remains the same.

tblBookings
Note TeacherID 1 associated with 2 schools.

ID	ShowID	TeacherID	SchoolID
1	1	1	1
2	1	2	1
3	1	3	2
4	2	1	1
5	2	3	2
6	3	2	1
7	3	3	2
8	4	1	4

However, a form/subform is not required. Can be just a form bound to tblBookings and then select all 3 ID's in each record. The form/subform simply allows one of the ID fields to be automatically filled in. This is accomplished with the Master/Child links properties of the subform container control.

Edit: I did not see your post with the ERD before submitting this post.
Who is the contact in the bookings table? Each show can be associated with multiple booking dates and mutiple contacts?

[Ruegen]

Your understanding of the relational table structure is not correct. There is only one field for the TeacherID in tblBookings. There will be multiple records for each ShowID. The combination of ShowID, TeacherID, SchoolID should be unique for each record in tblBookings.

Regardless of which form arrangement used (can incorporate all 3 in the db), the table structure remains the same.

tblBookings
Note TeacherID 1 associated with 2 schools.

ID	ShowID	TeacherID	SchoolID
1	1	1	1
2	1	2	1
3	1	3	2
4	2	1	1
5	2	3	2
6	3	2	1
7	3	3	2
8	4	1	4

But then I have to choose a show each time for each school/teacher to the same booking.....?

[Ruegen]

Like so? Or just feed the show ID from the tblBooking?

[Ruegen]

Attachment 13238

unless you are thinking it is this structure...

[June7]

Use form/subform. One of the ID's will be automatically filled in. I edited my previous post with comments re form/subform.

If you are associating teacher with a single school then don't see need for the SchoolID in bookings. Your first ERD makes sense, not the later ones. ShowID should not be in both tblBookings and tblTeachers.

My original thoughts on structure are in post3.

[Ruegen]

If you are associating teacher with a single school then don't see need for the SchoolID in bookings. Your first ERD makes sense, not the later ones. ShowID should not be in both tblBookings and tblTeachers.

Yes I will make sure ShowID is only in one - tblBookings I think. I only didn't remove it just to quickly take a snapshot - however curiously would access get confused by any chance?

[Ruegen]

I did not see your post with the ERD before submitting this post.
Who is the contact in the bookings table? Each show can be associated with multiple booking dates and mutiple contacts?

There is no contact in the bookings table - (ignore anything you see in the table). In the past we used to just make one booking and put the contact details and have schoolsID in the booking table in the booking however we would like to add more than one school to any single booking each with their own contact which would be on the fly since one school might ring up twice - i.e. a music teacher might ring up from the same school and book a different show to the math teacher who previously booked so I can't keep the name in the school table.

Yes each booking can be associated with multiple contacts, just one date for each (these are schools communicate with each other that travel to one school who hosts the show).

Because the company also represents theatres this whole structure would be the same. We hope to make a booking with a theatreID and then add schools to the booking and as they do come in we type in the school attendance to the event which then calculates how many seats are left at that theatre - so if it seats 200 and we apply a school to that booking wanting 90 seats, we then minus that from the theatre seats and have that tell us how many seats are remaining to fill - as schools often cancel I need to be able to delete them from the booking as time moves on.

So I'm hoping if I get this part done correctly then the theatre bookings will follow suit.