Linking two tables

[MikeB]

I have a table [Admins] with a field AdminID and a table [Clubs] with
a key field ClibID. An Admin can manage one or more clubs. and there
can be more than one Admin for each club, Admins are either the
teacher in the school that has responsibility for the chess club, a
coach, or a parent volunteer, for instance.

I figure I need a table ClubAdmins to correlate who's who. But how do
I create the form that connects the two tables?

I can create a form to add/manage admins and I can create a form to
add/manage clubs, but I can't figure out how to add the ClubID and the
AdminID fields into the ClubAdmins table.

Any help gratefully received.

 

[John W. Vinson]

I have a table [Admins] with a field AdminID and a table [Clubs] with
a key field ClibID. An Admin can manage one or more clubs. and there
can be more than one Admin for each club, Admins are either the
teacher in the school that has responsibility for the chess club, a
coach, or a parent volunteer, for instance.

I figure I need a table ClubAdmins to correlate who's who. But how do
I create the form that connects the two tables?

Yep. You need a table ClubAdmins with fields AdminID and a field ClubID to
model the many to many relationship.

I can create a form to add/manage admins and I can create a form to
add/manage clubs, but I can't figure out how to add the ClubID and the
AdminID fields into the ClubAdmins table.

The simplest way would be to use a Form based on Clubs, with a Subform based
on ClubAdmins. This would have the ClubID as the master/child link field, so
that any new record added will automatically fill in the current club's ID; on
the subform you would have a combo box based on Admins to select an
administrator. You could have code in the combo's Not In List event to pop up
the administrator data entry form to enter a new administrator if you didn't
already have that person in the table of administrators.

 

[MikeB]

The simplest way would be to use a Form based on Clubs, with a Subform based
on ClubAdmins. This would have the ClubID as the master/child link field, so
that any new record added will automatically fill in the current club's ID; on
the subform you would have a combo box based on Admins to select an
administrator.

This is the part that I don't get. I've previously used subforms to
display a one-many relationship and that was easy. I simply defined
the relationship and then put the subform in the main form and it
worked.

Now if I have a form for Clubs, if I try and put the Admin form in as
a subform, that doesn't work since there is no direct defined
relationship between the two forms. So I tried to create a subform
using ClubAdmins table. That seems to allow me to put the subform in
the form, but then I got stuck again. Do I create another nested
subform for the Admins table?

I'm missing a cognitive step here.

You could have code in the combo's Not In List event to pop up
the administrator data entry form to enter a new administrator if you didn't
already have that person in the table of administrators.

This is taking it to a whole new level. I'm going to have to do a LOT
of reading before I grasp this .

 

[John W. Vinson]

This is the part that I don't get. I've previously used subforms to
display a one-many relationship and that was easy. I simply defined
the relationship and then put the subform in the main form and it
worked.

Now if I have a form for Clubs, if I try and put the Admin form in as
a subform, that doesn't work since there is no direct defined
relationship between the two forms. So I tried to create a subform
using ClubAdmins table. That seems to allow me to put the subform in
the form, but then I got stuck again. Do I create another nested
subform for the Admins table?

I'm missing a cognitive step here.

Well... typically you will have one of the "one" side table where you're
always going to add a new record, and another "one" side table that's more
like a lookup table: there will ordinarily be a record already there and
you're just selecting it. The first of these would be the mainform's
recordsource. That is, if the way your database works is that you'll
ordinarily be adding a new club, and then assigning existing administrators to
it you would use the Clubs table as the mainform; if on the other hand you
have a list of clubs, and you want to add new administrators and assign them
to a club, you would make Administrators the recordsource for the main form.
In either case, the subform is based on ClubAdmins and you will - again,
usually - be just specifying the relationship, not adding to the other "one"
side.

What you can do is use the NotInList event of the combo box on the subform to
pop up a data entry form for the new administrator, or for the new club.

If the "far side" table doesn't have very many fields, you can base the
Subform on a Query joining ClubAdmins to (say) Administrators. If you include
both tables' linking fields in the Query, and make it a Left Outer Join ("show
all records in ClubAdmins and matching records in Admins"), you can actually
add a record to both tables in one operation. This gets tricky if there is
much information to add (e.g. if the Administrators form has subforms for
multiple addresses, multiple phone numbers, etc.).