Actually I was using the parts table as an analogy. I have 4 tables.
Their structures are as under
Analyst master
Analyst firm, first name, last name, address
Analyst sessions
Analyst firm, first name, last name, session ID, practice ID
Session Master
Session ID, session start time, session end time
Practice Master
Practice ID, Practice description
Analyst master -> analyst session (one-to-many). Each analyst can have
multiple sessions with multiple practices.
But are the First Name, Analyst Firm, Last Name field properly
attributes of a Session? or of an Analyst Master? That is: might you
have an Analyst Master record related to an Analyst Session record,
but have the names different in the two tables?
If not - then the name fields are redundant. Better would be to have
an artificial key (AnalystID, maybe an autonumber) in the Analyst
Master table, and a foreign key AnalystID (long integer if you use an
Autonumber) in the Analyst Sessions table.
I tried to create a master-detail form using sub-forms. In the detail
form I have data from "analyst sessions" and have session ID & practice
ID displayed on each row. However, I do not know how to fetch the
descriptions from their respective masters
Again the simplest way is just to use a Combo Box on the subform. If
you have a Combo with the Practice Master table as its Row Source, the
PracticeID as its Control Source, and the column widths property set
so that the PracticeID is of zero width and the Practice Description
is the only visible field, then the user will see the description; the
computer will see the ID; and they'll both be happy. It's easiest to
do this if you use a Continuous Form (rather than the wizard-provided
datasheet) as the default view of the subform.
If for some reason you don't want to do so, you can still display the
description; include the description in the combo (with zero width in
the ColumnWidths if you don't want to see it there at all), and put a
textbox on the subform with a control source
=comboboxname.Column(n)
using the name of the combo box, and using n as the *ZERO BASED* index
of the description field (i.e. if it's the second field in the combo's
rowsource use (1).)
John W. Vinson[MVP]