join file/portal/many-to-many relationship

[susan]

I'm trying to write a solution that will track scores on graduation

standards. There are 9 standard areas. Each student in each grade (K-6) does one assignment in each standard each year. So I have 1 class with many

students; 1 student with many standards; 1 standard with many assignments (one for each grade); all tracking across multiple years.

The final report will be a sheet for each standard/student (each student will have 9 pieces of paper in his/her file.), listing results for that standard from each grade.

For data entry, I would like to be able to have a layout with a pop-up menu for the standard area. The user selects the standard and the assignments for that standard appear. Or, I could have a pop-up menu for the student and when any student is selected, the standards appear. Make sense?

I know I need one or more join files and portals, but I just can't get my brain around what goes where. Any help would really be appreciated. Thanks.

Susan

[LiveOak]

The classic example used for a join file is Students and Classes. Each Student has many classes and each Class has many students. This many-to-many relationship is resolved using a join file in which a record is a Student/Class combination. In your case you also have a Standards file. Each Student has multiple Standards scores and each standard has scores for multiple students. So, it sounds like you need a Standards file and a join file with Student in which a record is a Student/Standard. Scores would be enter into records in this file. You can have a portal from Students. In this case you enter a standard and score. If your portal was from Standards, you would enter a Student and a score. If you did the entry directly into the join file, you would have to pick both a Student and Standard and enter a score.

-bd