Stepping out into calculations!

[andyw]

As I step out in using calculations some help would be appreciated. I am trying to do a calculation that will multiply the course number by a set fee, i.e.

course number * 350 for example.

However, and here I get stumped, the fee is a variable depending on a certain field called scholarship, which has a number of options like "ETC" or "EKHC". I have got the following calculation to work, using IF, so that when "ETC" appears in the scholarship field it will return a result of course number * 350.

course name *

If (Scholarship = "ETC"; "350" ; "260" )

This works great! But what I am wondering is how I can add additional IF qualifiers to the same calculation, such as If..."ETC" it chooses 350, but if not then it chooses 260. At the same time it checks to see IF..."EKHC" and then chooses 350 or if not then 260. Is it possible to put several IF qualifiers in the calculation?

Thanks for any help.

[comment]

You are describing the Case() function. However, your overall approach is problematic: fees are data, and they change over time. It's not a good idea to hard-code them into calculations. You should have another table of scholarships and their associated fees, and have a local field lookup the fee from there via a relationship.

BTW, multiplying the course number or its name(!) by a fee doesn't seem very logical.

[Simon K]

If you were going to do this calc in this way you could use nested if's, but a CASE calc would be much easier to read:

Coursenumber/name *

Case(

scholarship = "etc" ; 350;

scholarship = "efgk" ; 350;

260)

However you need to be clear on what you are trying to do and without knowing more, I would add that you should not be hard-coding scholarship codes into the calculation - they should be maintained in a separate table so that you can add/edit them easily in the future.

Multiplying the value by the coursename/number doesn't make sense - I would have thought you should have a basic course fee which is modified by the scholarhsip - and that the scholarship should/may vary from student to student and year to year

[Simon K]

Crossed with Michaels response - which says it much more briefly than mine!

[andyw]

Thank you for this. From what I am reading I should only use calculations with fixed data, rather than having to keep changing information in the calculation each time. In setting student fees, I am trying to set up three columns if you like. The first indicates the total fees due in any one semester. The problem with this is that course fee total (number of courses a student is taking * fee) depends on whether they are a scholarship student or not since we have a two-tier fee system. The normal higher fee for those on scholarship and the lower fee for those not on scholarship. The second column would indicate how much of the fees due is paid by the scholarship (if applicable) and the third column would indicate what the student must pay themselves. Instead of putting "350" in the calculation (which is the scholarship fee) could I have the result from the Case calculation pick up another field which would be the current scholarship rate? Or do I need to rethink the way I am approaching this? Thanks for your help

[comment]

We don't have enough information from you to form a full picture. In general, there would be (at least) three tables: Students, Courses and Enrollments. Enrollments has a record for each instance of a student enrolling in a course.

The current "listprice" for each course belongs in the Courses table, and is looked up into an enrollment record. In your case, it would be also necessary to lookup the student's scholarship status (assuming it can change in-between enrollments). Thus, the calculation of the actual fee is done entirely within the enrollment record, and does not depend on any related data that might change.

To get semester totals per student, you would produce a report from the Enrollments table, summarized first by semester, then by student.