Formulas and Calculations via Table or Query?

[AE]

This is my second question on the student database I'm
creating--specifically the SCHOLARSHIP information.

I want to avoid having to convert data into an Excel
spreadsheet in order to calculate scholarship offerings,
as data is subject to change on an hourly/daily basis.
I would think that the whole process could be self-
contained in the student database, through table, query,
and report functions--allowing for accurate reporting on a
moment's notice.

I need to be able to track all data that is used to
determine a student's "score" for scholarship
consideration. I've created the necessary fields for data
entry (i.e. Income, Audition, NumOfClasses, NumOfYears).
Now I need to translate each bit of data into a
predetermined score, and then weight it. Can this be done
in additional fields in the same table, or should it be
contained in another table or a permanent query?

Basic Example:
If "Income" is < $40,000. and > $30,000.00
then "IncomeScore" would automatically = 10
If "Audition" = "A" then "AuditionScore" would
automatically = 20.
There would then need to be a field that automatically
updates with the "TotalScore"

Again, any help or referral to a source of examples would
be appreciated.

ae

 

[John Vinson]

Can this be done
in additional fields in the same table, or should it be
contained in another table or a permanent query?

Calculations cannot be done in a Table, and the results of
calculations should not be stored in a Table. However, it's perfectly
standard to do calculations in a Query.

You can simply create a Query based on your table (or on two or more
joined tables); in a vacant Field cell type a name for the calculated
field and the expression that does the calculation.

You may need to consider having some tables wherein to look up values;
a "range table" can be handy for some of the things you're doing. For
example you could have a table IncomeRange with three fields - Low,
High, and Score with values like

30000, 40000, 10

You could then create a Query linking this table to your student table
using a "Non Equi Join":

SELECT <a bunch of fields>, IncomeRange.Score
FROM Students
INNER JOIN IncomeRange
ON Students.Income >= IncomeRange.Low
AND Students.Income < IncomeRange.High;

You can't do this in the query grid but must use the SQL window
instead.

 

[AE]

I'll need another cup of coffee to digest this
information, but I actually understand some of it on first
read through.
Thank you.
ae

-----Original Message-----