Subreport Linking

[Mathew Winder]

I have a report that lists instructors from a table by the date of their
latest class. There is a subreport that then lists the classes they've
taught so that master field is the instructor ID from the instructor table,
and the child field is the instructor ID from the classes table.

The problem I'm having is that each class record can have up to two teachers
contained in two different fields. I've got it to link the classes based on
the first teacher field, but some of the teachers' latest class date is for a
class in which they were the second teacher (and thus contained in the second
field). What I need is for the report to link to the first field as it is,
but then if that is empty I need it to link to the second field.

Is something like this possible? Any assistance would be greatly appreciated.

 

[Duane Hookom]

You need to normalize your classes table like:

SELECT ClassID, Instructor1 as Instructor
FROM tblClasses
UNION ALL
SELECT ClassID, Instructor2
FROM tblClasses
WHERE Instructor2 Is Not Null;

Join this back to your tblClasses so you have a record for each instructor
for your subreport.

 

[Marshall Barton]

I have a report that lists instructors from a table by the date of their
latest class. There is a subreport that then lists the classes they've
taught so that master field is the instructor ID from the instructor table,
and the child field is the instructor ID from the classes table.

The problem I'm having is that each class record can have up to two teachers
contained in two different fields. I've got it to link the classes based on
the first teacher field, but some of the teachers' latest class date is for a
class in which they were the second teacher (and thus contained in the second
field). What I need is for the report to link to the first field as it is,
but then if that is empty I need it to link to the second field.

Add a hidden text box to the main report section containing
the subreport and set its control source expression to this
kun of thing:
=IIf(teacher1 Is Null,teacher2,teacher1)
Then set the subreport control's LinkMasterFields property
to the name of the hidden text box.

Note that this is a hokey workaround for the un-normalized
design of your table. You should not have the teacher info
in the class table. Instead you should have another table
(named ClassTeachers) that has two (or more) fields. One
for the foreign key to the class table and another for the
foreign key to the instructors table. Other field could be
used for data related to a specific teacher/class
combination.

 

[Mathew Winder]

I was able to use a union query working in the sub-report that solved the
problem - thanks for the tip!