Outputting fields on separate lines

[JRickman]

We have a Guardian table which contains the following fields:

Guardian_Record_ID
StudentID
G1_Name
G1_Relation
G1_Phone1
G1_Phone2
G1_Email
G2_Name
G2_Relationship
G2_Phone1
G2_Phone2
G2_Email
G3_Name
G3_Relationship
G3_Phone1
G3_Phone2
G3_Email
GuardianRecordLastUpdated


We need the report to be formatted as follows (we have a query which gets LastName, FirstName, phone, grade, and language from other tables):



STUDENT: LastName,FirstName PHONE: GRADE: LANGUAGE:
G1_Name G1_Relation G1_Phone1 G1_Phone2 G1_Email
G2_Name G2_Relation G2_Phone1 G2_Phone2 G2_Email
G3_Name G3_Relation G3_Phone1 G3_Phone2 G3_Email

How do we get the G1_ , G2_, and G3_ records on separate lines? We may possibly have entries for up to 7 different guardians/contacts. Also, there appears to be a limit in Access to only report the fields which fit onto a printed sheet. Thus, the G3_ fields are not output in the report.

Forgive what is probably a very beginner-type question. I'm new to Access 2010.

Thank you very much, Jim

 

[Beetle]

Your problem actually begins with your table structure. You shouldn't have repeating fields like that. If a Guardian/Contact can only be associated with one student, then you would have a separate table with a structure like the following;

tblGuardians
GuardianID (Primary Key)
StudentID (Foreign Key to tblStudents)
GuardianName
Phone1
Phone2
EMail

On the other hand, if a Guardian can be associated with more than one student, then you need a third (junction) table to resolve the relationship.

This link has some good information on table structure and normalization, among other things.

 

[JRickman]

Thank you much. I've narrowed the size of the table as you suggested and, voila, that gives me a lot more flexibility when doing queries and reports.