Subreport Linking

Mathew Winder · External Since: Oct 16, 2009 Posts: 2

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 · External Since: Feb 07, 2005 Posts: 1359

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.

--
Duane Hookom
Microsoft Access MVP

"Mathew Winder" wrote:

> 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.

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

Mathew Winder wrote:

>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.

--
Marsh
MVP [MS Access]

Steve · External Since: Aug 04, 2009 Posts: 47

You need to revise the design of your tables. Since a class may have more
than one teacher, you have a one-to-many relationship between classes and
teachers. Your tables should be:
TblTeacher
TeacherID
etc

TblClass
ClassiD
etc

TblClassTeacher
ClassTeacherID
ClassID
TeacherID

Steve
santus.TakeThisOut@penn.com

"Mathew Winder" <MathewWinder.TakeThisOut@discussions.microsoft.com> wrote in message
news:8DDB260A-8269-46C0-A8FB-FD7E0B7796C1@microsoft.com...
>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.

Mathew Winder · External Since: Oct 16, 2009 Posts: 2

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

"Duane Hookom" wrote:

> 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.
>
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "Mathew Winder" wrote:
>
> > 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.