Junction tables

[J]

Hi,
I have two tables, Workshop and PresenterInfo. The
primary keys for both tables, workshopID and presenterID
respectively, are Autonumber fields.

I also have a WorkshopPresenter table which is a junction
table for the above two tables. This table has three
fields. viz. ID (autonumber) and the two keys from the
first 2 tables. The combination of workshopID and
presenterID forms the primary key in my junction table.
I have defined one-to-many relationships between the
above 2 tables and this table to achieve many-many
relationship between the first 2 tables. I have also
enforced Referential Integrity with Cascade Update/Delete
Records options.

I am using ASP forms to input information first into the
Workshop table and then add presenters to the
PresenterInfo table. My problem is that new records added
to the first 2 tables are not being written to the
junction table.

Could someone please help me out here? Any and all help
is appreciated.

J

 

[J]

Thanks for your reply Jeff.

Let me explain the setup a little.
Please note that the following is true from the user side
only. There is an Administrator's side with different
functionality.

My predecessor set this thing up in a 2-step process:
1. First, you go to an ASP form and add a workshop that
you want to present. This gets added to the 'Workshop'
table and a new autonumber becomes its WorkshopID
(primary key).

2. Then you add a presenter for this workshop. On this
page, you are made to select the workshop that you just
added from a drop-down list. The presenter then gets
added to the 'PresenterInfo' table and is assigned a new
autonumber 'presenterID' (primary key).

3. What the junction table then holds is these 2 values
of WorkshopID and presenterID alongwith a third value
which is again an autonumber field. So a record in the
junction table would have the WorkshopPresenterID
(autonumber field value), the newly added WorkshopID and
the newly added presenterID.
This record, I was told, is not written by anyone through
any form on the user side but gets created automatically
as soon as a new Workshop-Presenter combo is entered.

The above procedure is repeated if more than one
presenters are added for the same workshop.

What you mentioned about existing presenters being able
to present new/existing workshops is true for the Admin
side where one can select WorkshopIDs and PresenterIDs
from 2 different drop-down lists on a form and then add
them to the junction table.

My only problem for now is to find out how the new record
in the junction table gets added from the user side since
the user only adds a new workshop and a new presenter and
does nothing to add/change anything in the junction table.

Hope the above explanation helps. Maybe I'm missing some
basic points completely, but I have been too stressed in
figuring out this huge website passed on to me.

Thanks your help.

J

So, in effect, the Workshop and PresenterInfo tables hold
only one occurance of each NEW Workshop or Presenter.
Relationships between the two are held in the junction
table.

 

[Jeff Boyce]

(see in-line below)

3. What the junction table then holds is these 2 values
of WorkshopID and presenterID alongwith a third value
which is again an autonumber field. So a record in the
junction table would have the WorkshopPresenterID
(autonumber field value), the newly added WorkshopID and
the newly added presenterID.

This sounds like you already are getting the junction table row with
WorkshopID and PresenterID.

This record, I was told, is not written by anyone through
any form on the user side but gets created automatically
as soon as a new Workshop-Presenter combo is entered.

I'd look into the underlying query that supports that data entry form, as
well as any event procedures for that form.

The above procedure is repeated if more than one
presenters are added for the same workshop.

What you mentioned about existing presenters being able
to present new/existing workshops is true for the Admin
side where one can select WorkshopIDs and PresenterIDs
from 2 different drop-down lists on a form and then add
them to the junction table.

My only problem for now is to find out how the new record
in the junction table gets added from the user side since
the user only adds a new workshop and a new presenter and
does nothing to add/change anything in the junction table.

(see previous comment)

Hope the above explanation helps. Maybe I'm missing some
basic points completely, but I have been too stressed in
figuring out this huge website passed on to me.

Thanks your help.

J

So, in effect, the Workshop and PresenterInfo tables hold
only one occurance of each NEW Workshop or Presenter.
Relationships between the two are held in the junction
table.

As it should be -- the Workshop table holds unique Workshops, the Presenter
table holds unique Presenters, and the junction table hold unique
combinations.

Good luck

Jeff Boyce
<Access MVP>