calculation with multiple IFs...

[e3digital]

I'm working on a solution that tracks student scores on various tests across 6 years. I want to get the current gain for a given test and am having trouble with the calculation. Here's the scenario:

The XYZ Reading Test is given in the Fall, Winter, and Spring of each grade, from K through 6th grade. I want the "XYZ Reading Gain" field to display the student's gain on that test at the present time.

For example, if the student has been enrolled since Kindergarten and is now in 4th grade, I want the gain to show her most recent XYZ Reading score (Fall of 4th grade), minus her first XYZ Reading score (Fall of Kindergarten).

I know the basic formula is XYZ Recent Score - XYZ First Score (these are not fields)

The catch, of course, is that not every student has been enrolled since Kindergarten, and the most recent test isn't always Fall. How do I account for those variables and get the accurate Gain?

Thanks for your help...

Susan

[mr_vodka]

One suggestion you might want to consider is to have a script do this for you. You can show the related records for the student and with a sort by Date script you can get the recent and first score. Then set a field in your Students file with the data. Just a possible suggestion.

[Ender]

If you have your scores in a related Test_Scores file, then this will be easy. Simply have a calc get the difference between the first related record and the last (have the relationship sorted by Date):

Gain (calculation, number result) = last(Test_Score::Score) - Test_Score::Score

If a student's scores are all in the same record, then you'd need a big case statement to figure out which scores are the first and last. If this is your structure, you should consider redesigning it with the related file to hold the scores. It not only makes this calc very easy, but makes it easier to do other types of reporting on the scores.

[e3digital]

Thanks for the suggestion. I do have all the test scores in one file/table. My structure is like this:

A Separate talble for Students, K-Data, First Data, Second Data, ... Sixth Data, Student Records. They are all connected via a Student ID number. Most of the viewing is done in Student Records.

There are 7 different tests and 13 test dates. (Some tests are only given once, others twice, and a few 3 times.)

If I were to redo my structure, what would it look like?

Also, when you say sort by Date, are you suggesting a date field for when the data is entered, or some other date?

Thanks again...

Susan

[Ender]

In general, when tables contain similarly structured data, they should be combined. If the data in each of your grades's tables is pretty similar, then those tables should be combined. You can add fields to distinguish between the grades.

What I'd recommend for the typical Student-Enrollment system is a table for the Student info, a table for the Enrollment info, a table for the Class info, and a table for the Test Scores. This way a Student can have more than one Enrollment record (one for each year,) and they can also have multiple tests. The Test Score record might just be a final score (with the Exam Date,) or you might include have a series of test questions in there that get recorded each time.

The final structure depends on what the needs are for your specific situation, but you want to try to "normalize" the structure as much as possible. More about normalization can be found on the web or in database design books.

[e3digital]

Ender,

Thanks for you help again... how patient are you? :

Would the fields in the Tests table go something like this:

XYZ Fall K

XYZ Fall K Date

XYZ Winter K

XYZ Winter K Date

XYZ Spring K

XYZ Spring K Date

ABC Fall K

ABC Fall K Date

ABC Winter K

ABC Winter K Date

ABC Spring K

ABC Spring K Date

XYZ Fall 1st

XYZ Fall 1st Date

XYZ Winter 1st

XYZ Winter 1st Date

XYZ Spring 1st

XYZ Spring 1st Date

etc.

eventually listing all the tests for all the grades and the dates? I see the value of having all the test scores in the same table, but this seems kind of redundant or something.

Also, would I use the same key for all the relationships?

Thanks,

Susan

PS--if you'd like to email me directly, I'm susan "at" e3digital.com

[Ender]

No, the fields in the Test Score table would be more like this:

StudentID (number)

ClassID (number)

Season (text)

Test Type (text)

Score (number)

Exam Date (date)

There is a one-to-many relationship between Student and Test Score, so each Student has multiple related Test Score records, one for each Season, Test Type, Date. The Student <=> Test Score relationship is based on StudentID, the Class <=> Test Score relationship is based on ClassID. These Test Scores could be entered through a portal either from a layout based on the Student table or a layout based on the Class table. You may also need an Enrollment <=> Test Scores relationship, to pull the last ClassID over into newly created Test Score records.

[e3digital]

OK... I think I have a better understanding now...thanks so much.

S