Student directory design

[MattB922]

Before burdening the list, I did some forum searching, but I did not come up with something directly on point.

I have been collecting the data for a school directory in the following tables:

1) family_info table (includes the last name, parents, address, contact numbers, emails, etc., plus a Family_ID serial number key)

2) student info table (includes the first name, grade, teacher, plus a Student_ID serial number key)

There is a one to many relationship between families and students (one family may have several students in the school). I have created a relationship between the student table and the family table based on the family ID.

Now I want to create a report that lists each family by last name, plus their contact information. Under each family name and contact info, I want to list students that belong to that family.

My thought was to report on students, grouped by families, but it has not been working:

APPLE Susie and Larry

.....123 Main Street

.....(312) 456-7890

........Billy.....Grade 2...Mrs. Crabapple

........Bobby.....Grade 4...Mr. Skinner

BANANA Betty and Barney

.....321 Fourth Street

.....(312) 678-1111

........Sam........Grade 3...Mrs. Jones

........Lonnie.....Grade 5...Mr. Pibb

I've tried a few approaches: a report by families with a portal on the kids; a report by students, grouped by families.

The grouped report doesn't perform as I expect - usually lists only one kid in the family. I also struggle with how to get the rest of the contact information into the group header. I understand that the family ID should be the summarized field, but how do I get the other fields related to the family in the grouping header. The "body" of the report would list the Students who are part of the family, plus their associated info (grade and teacher).

The portal approach frustrates me with too much white space (sliding?)

Does anybody have a recommendation for which approach is better? Am I on the right track?

Many thanks in advance.

[Fitch]

Portals can be set to slide up, however the better approach is generally to print from the child table (literally the child table in this case).

In the child table:

Sort by last name and by family ID.

Put the family info on a subsummary part sorted by family ID.

Put the child info on a body part.

[MattB922]

Thank you for the lightning quick response.

Do I understand you correctly that I can put multiple fields from the "parent/family" record in the sub-summary part (so the last name and addresses, all in the sub-summary part). I had what is obviously a mis-impression that the only field that could up there was the field being used to group (i.e., the family ID). So what drives how the grouping is working, the reference from the child table to group in family ID?

[Fitch]

Correct, you can put related fields in the subsummary part.

Grouping is driven by the sort order, which can contain multiple sort criteria. You want an alphabetical listing, so make last name the first criteria. However, you need the family ID to drive the grouping, so sort by that second, and make the subsummary part based on that as well.