Multiple Autolookup Queries on One Form

[Kim]

I'm setting up a database for a conference to track presenters and breakout
session info. I only have two tables - tblSessions and tblPresenters. Each
session could have up to 4 presenters so in tblSessions, I set up four fields
for presenters (Presenter1, Presenter2, Presenter3, Presenter4) that are
combo boxes pointing back to tblPresenters. I want to create a form that will
show all the session details and all the presenter details. I set up an
autolookup query to automatically fill in presenter contact info on my
frmSession but I can only get it to autofill and display contact information
for Presenter1. I've tried setting up four different lookup queries - 1 for
each presenter - but when I put them into a form, I can see them in design
veiw, but when I click on form view I just get a blank page. What am I
missing? Or is there a better way to do this?

Any suggestions would be GREATLY appreciated!!

 

[Duane Hookom]

You are missing the concept of normalization. Rather than having multiple
presenter fields, you should have a third table (a junction table) that
contains the primary key field values from tblSessions and tblPresenters. You
can then add as many (or few) presenters as needed.

 

[Ron2006]

And then later on when the users ask you to create a report that shows
you ALL of the sessions for which a certain person was one of the
presentors you simply look in that one table and can show all of the
sessions. If you tried it with your current design you basically
would have to query/check all four fields which would complicate your
queries.

Ron

 

[Kim]

Thank you for the advice... I feel like I'm getting closer to what I want,
but still can't quite figure out how to put all the pieces together!

I deleted the four presenter fields in tblSessions and created one
PresenterID field which is also the primary key in tblPresenter. I also made
a junction table containing only two fields - PresenterID and SessionID.
Then I made a qryPresenterLookUp query with the PresenterID and SessionID
fields from the junction table and the other fields from tblPresenter that I
want to autofill in my form.

When I created the form, I took the fields from tblSessions that I wanted
and qryPresenterLookUp. It created a form to fill in the session information
with a subform based on qryPresenterLookUp but there's nowhere I can enter
any data into the subform. What am I missing yet?

Again, thanks so much for the help!

 

[Duane Hookom]

There should be no Presenter field in tblSessions. I would expect you to have
a form based on tblSessions. This form would contain a subform based on your
junction table with Link Master/Child set to the SessionID field. The
PresenterID in the subform would be bound to a combo box that allows
selection of presenters.