Calculation fields from related fields

[teckert]

Well I'm Bummed, I entered this question before and it didn't post, so here we go again

I have a contact database for a local university, each student in this school of the university is required to take certain test in order to be certified. This would be similar to taking the SATs or ACTs for college entry. Each student make take the multiple tests, multiple times, the best scores are their actual rating. i have stored all of these test results in a related table each record represents a different test type with the associated date, score and other non-crucial information.

what i need to do is now determine which student HAVE or HAVE NOT met the criteria to move to the next phase. i could write a script to check every record to see where the students stand, however based on the number of records that exit, it takes a significant amount of processing time.

what i would like to do is use a calculation field in the main database which checks all of the available scores for a particular test type from the related table and return the score and the test date.

thanks in advance for your help

this forum has been extremely helpful in the past

[Dan-A]

Here's a thought...

Use the MAX function in a calc field to return the highest test result

Let me look at stuff i have done in the past to try to give you more detail

i'll post back later...

Dan

[Dan-A]

If you did a self-join on say: studentID

& you had a calc field with something like

Max(selfjoin::StudentResult)

the max function with return the best score for all related records...

So if you have 10 exams entered for student 12345 you would see the

best of the 10 results.

HTH

Dan

[teckert]

that will work if there is only one test type, however if i want to select a particular test type within the group, then i cannot be selective.

I am thinking of setting up a conditional relationship where i fill in the test type(s) that i want to look at then i can do a max based on that relationship

[Dan-A]

I think i missed your point...

I thought you were interested in getting the best result no matter wht test.

How about a field called t_testName then in your relationship

could be the self join from previous post AND testName.

So you would put the test you want to "search" in a global field; link it

to t_testName.

You should get all related test results for Student = 1234 AND testName = "thisSAT"

the Max(selfjoin::StudentResult) should give you the highest score for the related records.