So the question I pose is this, does this mean
it's a junctional relationship "many to many"?
Yes you can look at this way. When talking about junctions and relationships
I always found it best to never actually use the term junction table.
Furthermore as you'll see while in theory you do have a many to many it best
simply to build up your related table as you go along, and you can't really
set up a relationship between two tables that's many to many.
So, we have:
as I explain this to find that it while it is a many to many, in fact when
you're building relationships there's really no such think it's only the
overall design if your tables that makes it a many to many, not the fact
that you can design or put a relationship that says that many to many
tblCalls - our table of calls to be made
tblCoaches - a list of coaches
If you look at the above two tables we not going to make a relationship
between the two tables.
The secondary table
(subform) is a list of the coaches who would take these calls.
the above tells me that we simply need a table that holds the list of people
who are going to be called. Lets call it
tblListOfCallingCoaches
It is this list that's going to be in our sub form. We can't use the list of
coaches, that's just a simple everyday list of coaches that are available to
make calls. That list of coaches has nothing to do with keeping a list of
which coaches are to be called.
So our main table is tblCalls
our sub form will be tblListOfCAllingCoaches.
Our tblListOfCallingCoaches will have 3 fields
id - (autonumber primary key - this is actually not going to be needed or
used, but you should place a id pk in all if your tables. Down the road
there are instances and queries that will rely on using the primary key).
tblCalls_id (plain jane regular long number field. This is actually the
field where we're going to place the value of the primary key of table
tblCalls - this how relations are built in MS access, and in fact this is
what they refer to as a foreign key. If you build a sub form correctly this
value will be set automatically to the pk of the tblCalls in the main form
as you add each new record).
coach_id (regular long number field. in this field this is where we're
going to type in primary key id of the coach who's going to make the call)
Of course it's a bit of a pain to go on to the coaches table and look up the
particular pk id each time for a particular coach, and then go back into
this thing and type in the coach id. So you can either put a piece of paper
on the wall listing all the coaches with their pk id. Even better is to
actually build this contorl on the sub form as a combo box. It is important
that I stress the idea of using a piece of paper manually typing in the ID
here, because if you think about this it not really a special relationship
here, but only a list of coaches we have.
It is also significant to note that when you delete a record in tblCalls,
then all of the corresponding reocrds in tblListOfCallingCoaches needs to be
also deleted. Thus you'll check the cascade delete button in your
relationships window when you build this.
Keep in mind when you delete a tblCalls record the table coaches will not be
touched, deleted, or affected in any way shape or form. In other words the
only reason why we have a table of coaches is for convenience sake so that
you don't have to type the number of the coach into the
tblistOfCallingCoaches. In a sense our tblcoaches is just a table that
allows us to look up values with ease. In fact he could delete the table
coaches altogether and make the coaches name a text field in
tbllistOfCallingCoaches and simply type the name in each time.
I spent a considerable amount of time pointing out how this table coaches is
just the list of coaches, and is not really in a sense involved in the
relationships here. And if you look a relationship window you'll not really
see a many to many here, you simply have a table called tblCalls, and we're
going to attach to that a list of calling coaches, it's that simple.
You can now see why I don't like the idea of many to many or junction
tables. If you press the problem in terms of what you need, then you never
even get to the point about juntion tables at all.
Second issue, once I have the tables linked when I add my subform it
is not creating a drop down of all the coaches I have entered in the
subform.
As I explained it you could manually type in the coaches' ID into the sub
form but that's a pain. You have to make the sub-form a continuous sub form
(not a data sheet view). You can then design this sub-form in a regular way
with regular controls. This includes dropping a combo box on the form. We do
this in the form design.
So, simply open up your sub form in design mode and delete the Coach_id
control box, and then use the wizard to build a combo box list based on
tblCoaches. As I said the table listing of coaches is for convenience sake
only (we actually could eliminate it from a whole application, but the you
have to type in the coaches name each time you need it for a call).
