creating a grid

[susan]

I'm not sure if this is the right place to ask this question...

I'm working on a solution which tracks a student's score on 16 different possible tests taken in elementary school (K-5). I have the following files: Students.fp5 (contains student name, student_id, grad year, race, gender, etc.); Tests.fp5 (contains student_id, test name, test score, schoolyear and various calculated combinations, such as studenttest, testschoolyear, studentschoolyear, etc.) My last file is Reports.fp5 which contains student_id and relationships to Students and Tests.

Using portals I can get most of the information I want. However, I would like to create a grid-type layout which has the tests listed down the side, and grade of the student (K, 1, 2, etc.) across the top, and the scores within.

One idea I had was to create a portal for each grade and line them side-by side, but I don't know how to tell the first portal, for example, to display the scores from Kindergarten, the second portal to display 1st Grade, etc. I want the user to be able to enter 1 key (probably student id) which will display all the data.

Actually, I ultimately want to display this grid for a group of students, selected by the user, with each student getting his/her own page. (Could I do this by using a different key (gradyear) for example, placing the student name in the header and making the body the remaining size of the page?

Thanks for any help.

Susan

[susan]

Well, half your assumptions were right! :-) I have unique IDs for each student, but their grade is simply a field in the Tests file. (There is one record for each occurance of a student taking a test. In the Students file students are listed only once, but if a student has taken 7 tests, he/she is accounted for 7 times in the Tests file. The grade of the student at the time the test was taken is stored in the Tests file; the graduating year of the student is stored in the Students file).

I know I could create a grade ID using a calculation, but then I can't use the field in a relationship.

Other options? In the Tests file do I need a separate field for Kindergarten, GradeOne, GradeTwo, etc. That seems kind of bulky, but I really need that grid layout....

Thanks for any advice.

Susan

[susan]

One more thing...I create all my reports in the Reports file via relationships and portals.

[Kurt Knippel]

I will make the following assumptions: You have given each student some unique ID, you have given each grade some unique ID.

Create a set of calculations (in both you student file and your test file) as follows:

cStudent_K = StudentID & "-" & KindergartenID

cStudent_1 = StudentID & "-" & FirstGradeID

cStudent_2 = StudentID & "-" & SecondGradeID

cStudent_3 = StudentID & "-" & ThirdGradeID

Then simply create a set of relationships based on each of these calculated fields. Put them side by each on a layout and you have your grid.