Finding, totaling and reporting scores

[lesterd]

I have a database with 40 fields which hold scores 0,1,2,3, and 4. I need to find out how many "0", "1", "2", etc., I have in each of the fields and print it out in a report. I have records for over 9000 students.

for example: I have a field "testin1" with possible scores of 0, 1, 2, 3, and 4 for 9000 students and this repeated in field "testin2" and "testin3", etc. for 40 fields.

Please help

[Mike Williamson]

Create a flag calc that returns a value of 1 if its value matches the desired score, (for example "3") and then sum these flags to get the count of all "3's.

[RussBaker]

Life would be easier if this was a relational system, but its do-able with what you have - just more work.

to deal with the testin1 example...

create a summary field CountOfStudent = Count of StudentID.

create a layout with a sub-summary part, sorted by Testin1 Score.

in the header, type "Testin1 Scores" and place the CountOfStudent field.

delete the body part.

place the Testin1 Score and CountOfStudent fields in the sub-summary part.

sort the records by the testin1 field (either ascending or descending).

preview your new layout with a script like:

Allow User Abort [Off]

Sort (your pre-defined sort0

Go To Layout [your new layout]

Pause

Enter Browse mode

Go To Layout [Original Layout]

You should see:

Testin1 Scores 1732

--------------------

4........321

3........145

2..........67

1........210

0..........42

You now need to repeat this process for each of the 40 questions, with appropriate field/name changes. This is where the relational system would have been better. In a relational system, each record would hold the result of 1 question for 1 student, so then from a parent file you could view all the results for a student in a portal, and do all your reports in the child file with 1 script, and 1 layout - instead of 40 of each. In this case, your layout would include a higher sub-summary part sorted by question number and then by score, so your report would look like:

Testin1 Scores 1732

--------------------

4........456

3........321

2..........27

1........111

0..........21

-----------------------------------

Testin2 Scores 1732

--------------------

4........321

3........145

2..........67

1........210

0..........42

-----------------------------------

Testin3 Scores 1732

--------------------

4........666

3........555

2..........44

1........333

0..........22

etc

It is possible to create a child file from your 9000 results using a script so you can do this - the child would contain 40 x 9,000 = 360,000 records but that isn't a problem. Send me an email if you need help on how to do it.

[lesterd]

OK - If I set up a relational data base for the scores - what should it look like.

I do know how to set up a relational DB. Do I create a field for each score? testIN1, tst IN2, testIN3, etc. and then each record comes in with a seperate score for each student ID? I am quite lost!

Thanks

Lester

[RussBaker]

The child file in this sort of system should contain your data broken down into the most basic of building blocks. That way, you will be able to address it from a variety of ways from one or more Parent files. Its always easier to join data togehter than to split it apart.

As a minimum, you need to have these fields in the Child: (sample data)

Student ID (BAKER013)

Question ID (IN1)

Score (3)

Date - probably, in case you repeat these tests again.

So, the results for one student for one test would occupy 40 records (assuming that there are 40 questions)

The Parent would store the Student ID, and have a relationship to the child based on this ID (plus date if you want to be able to segregate repeats of the test) sorted by Question ID. Then have a portal showing the Question ID and the Score in each row. You could (outside of the portal) also have a field in the Parent which shows the average of the scores using =Average(Relationship::Score), or the Total, Minimum, Maximum etc - all done the same way.

Your reports for individual question scores across the whole file would be done in the Child File using the sub-summary reports as per my previous post.

I imagine you have another file that stores student personal information - so that the Student ID in you parent file could also get all this data from that file. That way, you only store information once.

[RussBaker]

Lester,

Have sent you a sample. If anyone else is interested, see attached. Open the Exams file first, click on the student name to get to the "form" type view with the portals...

LesterD.zip