subsummary report for portaled data

[summergirl]

Here's the deal:

File 1, Main, collects student demographic info. Data is also entered into 2 other files, Classes and Tests, via portals in Main. Students may have multiple records in the Class file, and multiple records in Test file. The key field for all 3 files is a single StudentID.

I need a report that sorts by the ClassSite field (resides in the Class file) and displays StudentName and TestName (from the Test file).

Attempt 1, in Class file: Created relationship ClasstoTest, matching the StudentID field in each file.

Created report in Class file, putting the ClassSite field in a subsum part; StudentName(from Test file)in a subsum part; and TestName(Test file) in the body part. Sorted by Class Site, :StudentName.

Result: Students are assigned to the proper class(es), but only the first TestName displayed.

Attempt2, in Test file: Created relationship TesttoClass, matching the StudentID field in each file.

Created report in Test file, putting related ClassSite field in a subsum part; StudentName (Test file) in subsum part; and TestName (Test file) in the body. Sorted by ::ClassSite, StudentName.

Result: TestName shows up for each test taken, but the student is only listed once, under the first ClassSite in his record.

For crying out loud, can someone PLEASE tell me what is wrong??!! Please type your answer s-l-o-w-l-y, and with lots of detail so I can understand the solution. Thank you in advance.

[RalphL]

I am having trouble with this setup. I think something is missing. There must be some relation other than StudentID that connects the Class to Test.

What you may have to do is create a report file that has a record for each student, class & test combination.

[summergirl]

Perhaps if I give a few details it will be more clear.

I created a new record in Main file for Jane Doe. A unique ID number (StudentID) is automatically generated.

Jane is attending Class A and Class B. When I enter this info in the Class portal in Main, two new records are created in Class, both with the same StudentID as the single record in Main.

Jane takes Test 1 and Test 2. After entering the test info in the Tests portal in Main, 2 new records are created in Tests, both with the same StudentID as the single record in main.

I would like a report to show:

Class A

Jane Doe Test 1

Test 2

Class B

Jane Doe Test 1

Test 2

I'll be the first to admit there are big gaps in my FM knowledge. I'm trainable, though... Thanks for you patience.

[summergirl]

Perhaps if I give a few details it will be more clear.

I created a new record in Main file for Jane Doe. A unique ID number (StudentID) is automatically generated.

Jane is attending Class A and Class B. When I enter this info in the Class portal in Main, two new records are created in Class, both with the same StudentID as the single record in Main.

Jane takes Test 1 and Test 2. After entering the test info in the Tests portal in Main, 2 new records are created in Tests, both with the same StudentID as the single record in main.

I would like a report to show:

Class A

Jane Doe

Test 1

Test 2

Class B

Jane Doe

Test 1

Test 2

I'll be the first to admit there are big gaps in my FM knowledge. I'm trainable, though... Thanks for your patience.

[summergirl]

*sigh*

Sorry for posting twice. Definitely not my day today.

[Ender]

Are the Tests tied to a particular Class? If so, this would imply a relational structure like this:

A Class can have many student Enrollments.

A Student can be enrolled in more than one Class.

A Class can have many tests assigned.

A Test can be taken by many different Students (Enrollments).

See the attached ER Diagram.

If this is correct, then your sub-summary report should be based on the Student-Test table.

[summergirl]

Are the tests tied to a particular class? Sort of. Students must be enrolled in at least one class, but are not required to test. Test results must be available for all classes the student enrolled in, not the just the one where s/he tested.

Student scenario 1: Jane Doe enrolls in Class A and takes a test. She then enrolls in Class B. In my Elusive Test Report, Jane's name and test results should appear for BOTH Class A and Class B.

Student scenario 2: Joe Smith enrolls in Class A but does not take a test. Joe's name should NOT appear on the report at all.

My current structure is

-Main file related to Class

-Main file related to Test

I have various reports from Class that work fine, as do those created in Test. I just can't seem to create reports that use data from both files on the same layout. Perhaps, as your illustration suggests, I need a 4th file to "blend" the info from Class and Test?

[Ender]

In that case, the ER Diagram would have a one-to-many relationship between Student and Student-Test instead of between Enrollment and Student-Test.

My first thought is that the report should be run from Enrollment, with a portal of Student-Tests (you'd need another relationship from Enrollment to Student-Test by StudentID for this in FM5.) Using a portal in a sub-summary report could give you some sliding issues, but I don't see another way to do this without duplicating data.

[summergirl]

I'm still working on this issue, but haven't resolved it yet. (Decided to take a little break and work on other design aspects to give my brain a chance to cool off.) Thanks SO much for your prompt replies, RalphL and Ender. Sometimes it helps to have someone force me to look at the problem from a different angle.