Report w/ related records not displaying correct data

[Jason H]

I have a report that is not behaving the way I want it to and I'm not sure why.

I have 4 tables that are all related to each other:
Tutor -> TutorCourse -> Course -> ProgramArea

"Tutor" contains the fields:
TutorID
FirstName
LastName
Email
TutorStatus (active or inactive)

"TutorCourse" contains the fields:
TutorID
CourseID
TC_ID
CourseStatus (active or inactive)

"Course" contains the fields:
CourseID
ProgramArea

"ProgramArea"contains the fields:
PA_ID
ProgramArea

(There are more fields in each table but for brevity I have only shown the ones I use in the report.)

The REPORT
I am trying to display:
-All tutors in a specific ProgramArea
    -Where Tutor::TutorStatus is "active"
    -Where TutorCourse::CourseStatus is "active"
    -where Course::ProgramArea is the ProgramArea specified by the user.

My report layout is set to use the "Tutor" table.

My results get:
-All the relevant tutors without duplicates (this is what I want)
-Only Active Tutors (this is what I want)
-"Most" of the records have the correct "Course::ProgramArea" but some display a different one despite the Find specifying a specific one.
-"Most" of the records have the correct "TutorCourse::CourseStatus" but some display "inactive" despite the Find specifying "Active".

Why is my report not working and how can I fix it?
Thank you!!
Jason

 

[Steve Martino]

I think you may need to have the report layout based on ProgramArea.  Just for checking, try putting those other fields on the ProgramArea layout and see if you can manually do a find for all the criteria.

[comment]

1 hour ago, Jason H said:

Most" of the records have the correct "Course::ProgramArea" but some display a different one despite the Find specifying a specific one.

If you are reporting from the Tutors table, then Course::ProgramArea will display data from the first related record in Course (unless you place it in a portal to the Course table, in which case it will display data from the corresponding portal row). If I understand your description correctly, a tutor may have many courses, and you have found tutors where at least one of the related courses meets the criteria - not necessarily all of them. 

 

33 minutes ago, Steve Martino said:

I think you may need to have the report layout based on ProgramArea.

No. If you want your report to list tutors, then it needs to be produced either from Tutors or from the TutorCourse join table (grouped by TutorID).