3 Replies Latest reply on Feb 21, 2011 10:59 AM by philmodjunk

    entered value triggers 1 of 3 calculations



      entered value triggers 1 of 3 calculations


      I am putting together a school marksbook from scratch with some great help and suggestions from forum members.  I have hit a bit of a wall trying to get a grade calculated.

      The database has a number of student records across different year groups eg 7, 8, 9, 10.  For each year there is a slightly different but rather lengthy "IF" calculation to determine the grade. I have the calculations so that is not the problem, i just cant seem to work out how to get the database to read the relevant year grouping and then apply the appropriate calculation.

      do i need to set up a calculation table that has two fields (eg: year    ,  Calculation) or is there some other way?



        • 1. Re: entered value triggers 1 of 3 calculations

          If you can redesign so that the grade calculation is the same for each year, life will be simpler for you.

          You could use a case function:

          case ( YearGroup = 7 ; //calculation for group 7 here ;
                    YearGroup = 8 ; //calculation for group 8 here ;
                    // and so forth

          But then you'll have to redefine this calculation to add a new expression for each new year group you add. That's cumbersome and can only be done by the developer.

          A lookup table like you describe with two data fields, Year, Calculation  (make calculation a text field) Could be used. Then this expression could compute the student grade: Evaluate ( calculationTable::Calculation ) for any given year group.

          Still think you shouldn't really need different calculation expressions for different year groups here....

          • 2. Re: entered value triggers 1 of 3 calculations

            Thanks Phil

            I will try the case suggestion.  I am unable to have the same calculation for all years as students are placed on a continuum and year group 10 are expected to have a higher achievement level for an A than year 7s.  The calculations are already written so that is not a huge problem (the expressions are the same just some of the values change) - will just cut and paste.

            • 3. Re: entered value triggers 1 of 3 calculations

              I don't see how "a higher achievement level" need require a different calculation expression. That value can simply be one more field referenced in the calculation.