Junction table/queries question.

[Spimby]

Hi....

I'm building a database with junction tables and I'm having a problem I
run into _every_time_ I build an Access database. I'm relatively new
to Access. I understand the use of junction tables, however I clearly
don't understand using queries to pull the data together.

Here's my situation:

Tables:

tblConference
tblPartcipants
(junction) tblConferencepartipants

The primary form is the "conference" form (frmConference). I want to
make it so that I can have the participants appear on the conference
form as a subform. So at the top, I'd have conference details and on
the bottom, I'd have the participant details. The participant table
has a unique participant ID (primary key, autonumber), the conference
table has a unique "Conference ID" (primary key, autonumber) and the
Junction table has it's own primary key (autonumber), as well as
numeric fields for the primary keys from the other two tables. What
I've been doing is creating a query for the sub report
(qryConferenceParticiants) and basing the subform on that.

The query pulls the confrenceID from the tblConference, the
PartcipantID from the tblParticipants and all the detail fields from
tblParticipatns I need (Name, employee ID, etc). I also pull the
Junction table primary key as well (autonumber).

This usually does not work. When I put the subform into the main form
and I click in it, I get the message:

"You tried to assign the Null value to a variable that is not a Variant
data type. (Error 3162)"

The bottom line is, I'm not sure which keys to pull and from where.
Should the conferenceID key come from the tblConfrence or the junction
table? The ParticipantID from the tbl Participants or the Junction
table? (by the way, the primary form is based on a query {just one
table} as well, not sure if that's an issue)

Thanks for (any) help on this. I'm lost.

Spimby

 

[Wayne Morgan]

Use the tblConferenceParticipants (I believe you had a typo in the name)
instead of tblConference for your query. You will need both the ConferenceID
and ParticipantID fields from this table. Add the tblParticipants (another
typo?) to this query and link it on the ParticipantID field. In the form
design view, open the Properties dialog, click on the subform ONE time to
select the subform control on the parent form. On the data tab of the
Properties dialog you will see Link Child Fields and Link Master Fields. For
Link Child Fields, enter the name of the ConferenceID field from the
subform. For the Link Master Fields, enter the name of the ConferenceID
field from the parent form.

In the linking table, you should have a "unique index" on the ConferenceID
and ParticipantID fields to prevent duplicates. This index will be on both
fields simultaneously. This will prevent duplicate pairs, but will allow
duplicates in each column individually. This is usually done by making these
two columns the primary key for that table, but can be done separately if
you've created another primary key field for the table. To do this
separately, open the table in design view, place the cursor in the field
name of one of these two field, and click on View|Indexes. In the resulting
dialog, enter a unique name for this index under Index Name. Under Field
Name enter the name of one of the two fields and set the Sort Order as
desired. If you have no preference, just leave it at Ascending. In the
bottom part of the dialog set Primary to No, Unique to Yes, and Ignore Nulls
to No. On the next line, leave the Index Name blank, but fill in the other
two with the information for the second field.