Columnar report with headers and summaries

[bisdwebmaster]

I am working on a solution that scores and tracks student test results (multiple choice test). I have two databases, test_key.fp5 and student_responses.fp5. Each test question has a unique identifier (concatination of test ID and question number). I have a one-to-many relationship established (based on question_ID) betweeen test_key.fp5 and student_responses.fp5. I have a simple calculation field (score) in student_responses.fp5 that checks the student response with the test key and returns a value of "+" if correct or the student's incorrect response if incorrect. I am having trouble creating reports for the data.

I would like a report that looks like this:

Question #____1___2___3___4___5___6___7___8___9

John Doe_____+___A___B___+___+___+___A___B___+

Jane Doe_____C___+___+___+___+___A___+___B___+

The closest I can come is to setup 40 columns (left to right) containing the field score and a leading sub summary when sorted by student_ID containing the student_fname and student_lname fields. The problem is, my report looks like this:

1___2___3___4___5___6___7___8___9

John Doe

+___A___B___+___+___+___A___B___+

Jane Doe

C___+___+___+___+___A___+___B___+

Any ideas how I can get the names to the left of the scores? Also I would like to have totals both along the bottom and the right side of the report so that a teacher could quickly see the number of students that got a particular question correct and the number of questions a particular student got correct.

Any help would be much appreciated.

[kennedy]

Not sure I understand your goal statement, but to the latter question...

Would this not work:

Lose the summaries and instead just add two columns to the left for student_fname and student_lname?

[bisdwebmaster]

Kennedy, Thanks for the response.

Maybe I am doing columns wrong. When I setup the layout, I choose 40 columns. I am only able to place fields in the first column. Whatever I have placed in the first column is repeated for each record in the subsequent columns. If I put student_fname and student_lname in the first column, name is repeated in each column. If it is a 40 question test each student's name appears 40 times, once by each question score.

Instead of having:

John Doe

+___A___B___+___+___+___A___B___+

I have:

John Doe + John Doe A John Doe B John Doe + John Doe +

Is there a different way to setup columns?

[kennedy]

Ohhh, I misunderstood you... when you said "setup 40 columns" you meant that you chose 40 columns in the layout setup. I thought you added 40 layout fields to the row... now I understand your question better. But not good enough...

Presumably you have multiple tests and each test has multiple questions... right? Does every test have exactly 40 questions?

Presumably each student takes each test zero or one times? Or might a student take the same test more than once?

The report you depict in your post just shows question numbers... are you filtering for a particular test, or are you somehow sorting by test to get that in the summary info... or is there just one test???

In this situation I could easily see having a student file, a test file, a question file (keyed by test id and question id), and an answer file (keyed by student id, test id, and question id). It sounds like you've not bothered with the student file or test file... the test info is embedded in the question file and the student info is embedded in the answer file. Do you have a separate test id field in the question file, or just the concatenated test-id and question number?

This is a great example of why I wish FMP had an Excel-like Pivot Table mechanism. Then you can easily put TestID and StudentID in the two columns of the X-axis and QuestionID in the Y-axis and the Answer in the intersection cells. That would let you see all the students' answers for a particular test together. Quick drag swapping TestID and StudentID resorts by student so you can see all the tests a student has taken together. Another quick drag of TestID up to the Y-axis, and then you'd have one row for each student and in each row you'd see each test's questions. And so on.

Creating 2D reports in FMP is not near as easy as I'd like it to be; and not near as easy as Excel.

Brian

[Fenton]

Sigh. You could get what you want, but you're not going to like it. If you create 40 fields, and 40 relationships (sounds like "The Walrus and the Carpenter"), you could have 40 columns, which you could then arrange how you want.

The 40 fields would have the Question numbers: 1, 2, 3... and the Test ID, and the Student ID.

Student ID & " " & Test ID & " " & 1

Student ID & " " & Test ID & " " & 2

These would be Unstored. They would be the left side of relationships.

The stored fields should be:

Student ID

Test ID

Question#

Grade

There should be 1 record for each question.

The main concatenated key is: Student ID & " " & Test ID & " " & Question#

It is the right side of all the relationships. We'll just call them StudTestQuestion1, StudTextQuestion2, etc..

1st column: StudTestQuestion1::Grade

2nd column: StudTestQuestion2::Grade

etc..

On a positive note (a small one) you don't have to sort and go into preview mode. All data is available in Browse mode.

You can use a similar method, with the Count Aggregate function, to get overall totals.

Total number of questions a student got correct (put at the end of the row):

Calculation Field: StudentID & " " & Test ID & " " & "+" (Unstored)

Calculation Field: StudentID & " " & Test ID & " " & Grade

Self-relationship on above, self_Student|Test|Grade

Count (self_Student|Test|Grade::Grade)

Total number of students who got a question correct [this would be a pain in this file; really belongs in the other file with the questions]:

40 of these:

Calculation Field: Test ID & " 1 " & "+" (Unstored)

Calculation Field: Test ID & " 2 " & "+" (Unstored)

Calculation Field: Test ID & " " & Question# & " " & Grade

40 relationships on above:

Test|Question1

Test|Question2

etc..

40 of these:

Count (Test|Question1::Grade)

I'm not sure about the "+", but imagine it would work in a text field. If not, use a "1" or something.

(P.S. The above may or may not be totally correct. Enough fun though; I really must do some work now :-)

[bisdwebmaster]

Thanks for the help. This worked great. You were right, it took some time but it's worth it.