Help needed!

[Martink]

I have two table:
GradeBoundaries(Subject, A, B, C, D, E, U)
Script(CandidateteName, Subject, Mark, Grade)

Each subject has different grade boundaries. I want grade to automatically update. How would this be possible.

I am new to access and would really appreciate the help even if you don't know an exact solution please point me in the right direction.

Thanks a lot Martin.

 

[Mile-O]

You can use an Update Query to calculate the grade from whatever base information/criteria you have.

 

[Rich]

Since grade is dependant on "marks", it's a calculated value and shouldn't be stored. Use a function or query to return the Grade

 

[Mile-O]

When is it okay to store calculated values?

Just a question that came to me because what if the award levels were changed and when running a query to calculate the resultant grades those in the past who had been awarded a mark under the old system would find their results changed with the new grading structure?

 

[Rich]

Depends on what you wish to do with the data. Unless you store the dates the grades/marks system changes any comparison is meaningless.

 

[Martink]

Detail

Could you please give me a little more detail on how i would go about implementing these suggestions.
Is it alright to store the grade boundaries?

Thanks again for the quick replies

 

[Rich]

The problem with the update method (apart from data integrity etc) is that without specific criteria ie date fields, you will update all the records anyway, so it's probably best to store the grade boundaries and the Dates they are created

 

[Martink]

But how do i do it?
Do i need to write a model in VB code or SQL? i just don't get it!

 

[Mile-O]

Personally, I would create a public function within a module with the following as a rough example:

Public Function AssignMarks(ByVal sngScore As Single) As String
   Select Case sngScore
      Case Is < 50
         AssignMarks = "E"
      Case Is < 60
         AssignMarks = "D"
      Case Is < 70
         AssignMarks = "C"
      Case Is < 80
         AssignMarks = "B"
      Case Is >= 80
         AssignMarks = "A"
      Case Else
         AssignMarks = "X"
   End Select
End Function

and in my query I would create a field called:

Grade: AssignMarks([Score])

 

[Martink]

There are many different subjects with different grade boundaries. How do i account for that?

 

[Martink]

Can i search my grade boundaries table for the correct grade?
i.e IF mark < value in field Math D then

 

[Martink]

What would be tghe correct syntax for this?

Thanks very much for the help so far, i don't need to list names you know who you are.