Adding data to tables with many-to-many relationship

[MikeB]

I think I asked this before, and I didn't understand the answer. So
please bear with me and point me in the right direction

I have a table of Clubs, each club can have one or more
Administrators. Each administrator can mange more than one club. I
created a table Clubs with fields for ClubID, Clubname. I created a
table Admins with fields for AdminID, AdminName. I created a table
ClubAdmins with fields ClubAdminID, ClubID, AdminID.

Can I create a form without VBA programming support to enter data in
tables Admins and Clubs and associate Clubs with Admins (or vice
versa)?

I tried creating a form Clubs, with Club data and then a subform for
ClubAdmins. This didn't work, especially not when I tried nesting at
3rd level another subform for Admins.

I have a working Admin form and a working Club form, but it is hard to
figure out how/if I should create a third form ClubAdmin and then use
comboboxes to associate the info.

Even more confusing, based on some reading of these forums/newsgroup,
I decided to create a many-to-many relationship (on typing this, I
realize it might have to be one-to-many) relationships between Admins,
and tables for Addresses, Email and Phone numbers. I'm sweating blood
trying to enter data into those and then correlating it with the
correct Admin person.

My first version of this chess club database was nearly good enough.
This second version is killing me. It is (to me) impossibly hard to
get it figured out and implemented. Aargh!

I have the following books, but perhaps there are better books on the
market?

Microsoft Office Access 2003 by Grauer and Barber,
MS Office Access 2007 Steb-by-Step by Lambet, Lambert and Peppernau,
and
Access VBA for the Absolute Beginner by Vine (I thought I was only
going to need this to write the program to do the ELO ratings, so I
haven't dug much into this one yet).

 

[Steve Schapel]

Mike,

...
Can I create a form without VBA programming support to enter data in
tables Admins and Clubs and associate Clubs with Admins (or vice
versa)?

No. This is not possible.

I tried creating a form Clubs, with Club data and then a subform for
ClubAdmins.

This is a good idea, and should work. You need to have the Link Master
Fields and Link Child Fields properties of the subform set correctly.
And on the subform, you can use the Admins table as the Row Source of a
combgobox for the entry of the Admin(s) for the main form's current Club

This didn't work, especially not when I tried nesting at

3rd level another subform for Admins.

No, this won't work.

You might find this article of relevance:
http://accesstips.datamanagementsolutions.biz/many.htm

I decided to create a many-to-many relationship (on typing this, I
realize it might have to be one-to-many) relationships between Admins,
and tables for Addresses, Email and Phone numbers.

This would imply that you want to record more than one address for each
Admin person. And more than one email address for each Admin person.
Is that correct?

I'm sweating blood
trying to enter data into those and then correlating it with the
correct Admin person.

Same priciple... you would use a Addresses subform and an Email subform
on the Admins form. And once again, you need to have the Link Master
Fields and Link Child Fields properties of the subform set correctly.

 

[MikeB]

Mike,


No. This is not possible.


This is a good idea, and should work. You need to have the Link Master
Fields and Link Child Fields properties of the subform set correctly.
And on the subform, you can use the Admins table as the Row Source of a
combgobox for the entry of the Admin(s) for the main form's current Club

This didn't work, especially not when I tried nesting at


No, this won't work.

You might find this article of relevance:http://accesstips.datamanagementsolutions.biz/many.htm

I think I found that article from a previous post you did here - I've
read it before and while I find that I understand the example of the
Loans table for library books, I'm at a loss as to how to define
another table that is not a join table between Admins and Clubs. There
doesn't seem to be any data that purely relates to that connection.
Nor does it help me in adding and relating the entities together.

My major problem is that typically when I want to add a new club, I
have to add a new administrator and I need to tie them together. I can
add a club on a Clubs form, I can add an administrator on an Admins
form and I can then relate them by opening and adding the two entities
on a thrid form, but this isn't how people work in real life - it
feels clumsy and awkward. It would feel more natural to add either
the administrator or the Club and then add the related entity in a
subform.

This would imply that you want to record more than one address for each
Admin person. And more than one email address for each Admin person.
Is that correct?

Well, each admin may have his school (work) number and a personal
number or two. If more than one admin works at the same school, they
share the school address and the school number. But having a table of
numbers and then having to use a separate subform to add the numbers
to the relationship table just is hard to do, it isn't easy to just
look at the number and know what/whose number it is.

Same priciple... you would use a Addresses subform and an Email subform
on the Admins form. And once again, you need to have the Link Master
Fields and Link Child Fields properties of the subform set correctly.

Well, this is what I thought the Wizard would do for the subform. I
don't know what "set correctly" implies. Surely it means that the key
field of the subform has to be the foreign key field on the master
form? What else?

Thing is sometimes the Wizard seems to bring up some semi-complete SQL
that it wants me to complete and I have no idea how to do that.

I'm about at the "screw it" stage where I get rid of all these fancy-
looking relationships that is really just making my life more
difficult. I thought good data design would make things easier, but
this is more difficult.

 

[Steve Schapel]

Mike,

As you stated originally, "each club can have one or more
administrators. Each administrator can mange more than one club." Ok,
so that's the real-life relationship between the data elements... that's
what we have to work with, and there's nothing we can do to change that
fact.

So, what word do you use to describe the concept of a person being the
administrator of a club? I don't know... Appointment? Office holding?
Administratorship? Portfolio? Doesn't really matter, so let's grab one
at random for our purposes here - Administratorship.

Now, each person can hold more than one administratorship. So there is
a one-to-many relationship between "Administrator" and
"Administratorship". And there is a one-to-many relationship between
"Club" and "Administratorship".

Managing one-to-many relationships is what the form/subform construct
excels at. So, the above one-to-many relationships can be managed via:
Main "Administrator" form, with "Administratorship" subform.
Main "Club" form, with "Administratorship" subform.

Ok, here's the funny thing... From the point of view of the
Administrator, "Administratorship" refers to the that administrator's
Club(s). Whereas from the point of view of the Clubs,
"Administratorship" refers to the that club's Administrator(s).

So the above two "Administratorship" subforms are not the same, as they
serve a different purpose/focus depending on which main form they are
on. But you can certainly have both set ups within the one application.

But, unless we get involved in some fancy code and stuff, that's about
the end of the line. You just can't do both at the same time.

So that's where you work out the best approach froim the user interface
point of view.

Maybe you would think that the core Clubs data in sort of more "solid".
So maybe you would do it like this... Use a Clubs form to enter all
the Club information, or add a new Club as applicable. And then you
would set up the application such that the main way to enter the
Administratorships is via the Administrator form. So you go to the
Administrator form, and scroll throught them, or otherwise find the one
you want, or enter a new one of they are not already there... and enter
the clubs that the administrator manages on the Administratorship subform.

On the other hand, you might do it like this... Go to the club on the
Clubs form, where you enter (be selecting from a combobox) the
administrartors for the club, this being done of course on the
Administratorship subform. And then you have some code, or else a
little command button, which, in the case where you try to enter an
administrator who does not yet exist in the database, will open the
Administrators form at a new record, so you can enter the person, then
return to the Clubs form and register then there as the Administrator
for that club.

Both of these approaches (or similar) are used extensively in Access
applications in order to resolve the kind of scenario you are grappling
with.

 

[MikeB]

OK, much clearer now. Thanks. I'll give it a whirl.