Help with Forms and Relationships

[geekhunter]

Please help me out with this one. FM forms are giving me fits.

OK, I have a relatively simple database to keep track of grades. Each student takes 3-4 tests per week. My wife needs to enter their grades for each test and then output results per student. The final report should be one report per student showing their performance on each test. The number of tests and type of tests vary week to week.

I have the following tables and fields:

Students Table with:

StudentID

StudentName

Report Table with:

ReportID

ReportName

StudentID

Grades table with:

GradeID

ReportID

TestID

NumberRight

PercentageRight

Test table with:

TestID

TestName

NumberQuestions

The relationships are:

Student (one to many) Report

Report (one to many) Grades

Grades (many to one) Test

The structure is very much like a typical customer-invoices-lineitems-products schema.

I CAN enter grades just fine by creating a portal in the Report table. The portal directly places fields from the Grades and Test tables. While it seems to work just fine, I can't create a layout that will produce just one week's set of reports. I see now that I should add a report date to the report table but that doesn't solve all of my problems. When I try and generate a columnar report with a page per student I don't see every grade and every test, I just seem to see the topmost test results.

Any help would be greatly appreciated.

Geekhunter

[RalphL]

I see a many to many relationship between student and test, with grade/report as the join table. The relationships should be student to grade/report is one to many StudentID as the key field and test to grade/report is a one to many with TestID as the key field.

[geekhunter]

Maybe I should be clearer. The following relationships exist...

Student one-to-many with Report where StudentID is the key.

Report one-to-many with Grades with ReportID as the key.

Grades many-to-one with Test with TestID as the key.

Does this make sense? Each report only has one student but each report has many grades. Each grade is associated with only one test but each test can have many grades.

Geekhunter

[Ender]

Run your report from a layout based on the Grades table. Use a Sub-Summary part by Student and a Sub-Summary part by Week, where Week is a calc in Report:

Week = Weekofyear( Report Date )

[geekhunter]

Thank you! Solved my problem. I really appreciate the help.

Geekhunter