Please Help Basic Problem

[essseeproductions]

Hi, i have this problem which i've been trying to sort for a long time
now searching around but havent found a correct way really to do it.
Basically I've got a table and i want two columns:

One will have an exam result place inside it

The other will calculate the grade based on the exam result, for
example if the exam result is below or higher than a certain number a
letter grade will be displayed.

I'm not sure but i believe the best way to do this would be via an IIF
statement inside a query. And then inserting the query into the table.
Is this possible? Or isit possible to use an IIF statement in a table.
Please help me i really need this problem solved as soon as possible.
Many thanks for all replies in advance it is seriously appreciated.

 

[Guest]

Hi,

You don't want to store a calculated value in a table - this is a bad design
and will cause problems later. You are correct that you can use an iif
statement to do this for you if you create a query. Use syntax similar to
this:

=iif(FIELD between 0 and 5, "A", iif(FIELD between 6 and 10, "B", "C"))

You will need to work out the correct syntax and breakup for your divisions,
but this is how you go about it.

Hope this helps.

Damian.

 

[Smartin]

Hi, i have this problem which i've been trying to sort for a long time
now searching around but havent found a correct way really to do it.
Basically I've got a table and i want two columns:

One will have an exam result place inside it

The other will calculate the grade based on the exam result, for
example if the exam result is below or higher than a certain number a
letter grade will be displayed.

I'm not sure but i believe the best way to do this would be via an IIF
statement inside a query. And then inserting the query into the table.
Is this possible? Or isit possible to use an IIF statement in a table.
Please help me i really need this problem solved as soon as possible.
Many thanks for all replies in advance it is seriously appreciated.

Hello,

The first answer is: don't. Do not, that is, store a calculated value
(letter grade) in your table. Use a query to get the letter grades
whenever you want. If you store the grade, and update an exam score
later, your grade field will be out of date.

In your query you certainly can use an IIf statement to obtain the
letter grade for every value in the table. The grade this query returns
is guaranteed to be an accurate reflection of the exam results whenever
it runs. Moreover, you can treat the query /just like a table/, using it
as the record source for forms, reports, etc.

 

[Guest]

Awesome! I have had exactly the same question and been trying to figure it
out all day - should have checked here sooner. I kept getting an error that I
had too many arguements in the iff statement - guess I had something wrong in
the syntax.

Can you tell the iff statement to calculate based on values less than or
equal to 5, and so on, rather than using "between" command? I found that
using between doesn't account for any value in the FIELD that's a decimal.
How would that syntax look? I'll try to figure it out tomorrow.

 

[John W. Vinson]

Hi, i have this problem which i've been trying to sort for a long time
now searching around but havent found a correct way really to do it.
Basically I've got a table and i want two columns:

One will have an exam result place inside it

The other will calculate the grade based on the exam result, for
example if the exam result is below or higher than a certain number a
letter grade will be displayed.

I'm not sure but i believe the best way to do this would be via an IIF
statement inside a query. And then inserting the query into the table.
Is this possible? Or isit possible to use an IIF statement in a table.
Please help me i really need this problem solved as soon as possible.
Many thanks for all replies in advance it is seriously appreciated.

I'd suggest a table-driven solution instead. Create a GradeValues
table with two fields: the numeric grade at the low end of each letter
grade, and the corresponding letter. This table could have a third
field for the identity of the exam if different exams have different
conversions. E.g.

CourseA; 0; F
CourseA; 55; D
CourseA; 65; C
CourseA; 78; B
CourseA; 91; A
CourseB; 0; F
CourseB; 70; D
CourseB; 78; C
<etc>

A Query with a calculated field

Letter: (SELECT TOP 1 GradeLetter FROM GradeValues WHERE
GradeValues.Course = yourtable.Course AND GradeValues.Score <=
yourtable.Score ORDER BY GradeValues.Score)

will find the letter corresponding to the score.

John W. Vinson [MVP]

 

[Guest]

Hi April,

You can have any function or set of statements in there that return a
boolean (true/false) value. eg:

iif((FIELD <5) OR (FIELD > 10), "Less than 5 or bigger than 10", "between 5
and 10")

Hope that helps!

Damian.

 

[Tony Toews [MVP]]

You don't want to store a calculated value in a table -

Not always. In this case you're correct.

But in a few other situations such as price and cost fields you do
want to store the value with the transaction. In these cases the
price and cost is the price and cost for that item at that moment in
time. After all either can change a few minutes, a day, a month or a
year later. So you want to store those at that time.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Jamie Collins]

Not always.

I agree but I think you are applying the wrong criteria...

in a few other situations such as price and cost fields you do
want to store the value with the transaction. In these cases the
price and cost is the price and cost for that item at that moment in
time. After all either can change a few minutes, a day, a month or a
year later. So you want to store those at that time.

In the OP's case, perhaps the criteria for determining a grade based
on an exam score will change a day, a month or a year later, therefore
storing grade would fit your advice.

In your example, you could retain a 'history' of prices in the 'items'
table and therefore would be able to calculate the cost of an item
retrospectively based on the transaction date rather than store it.

The rule of thumb is to consider the model used (current state tables
or history tables, high insertion) and on performance e.g. is the
calculation faster than a disk/network read, etc. Of course, you can
always change the design or move to another platform... In other
words, it's difficult to make a recommendation based on so little
information, hence the 'rule of thumb' rather than 'hard and fast
rules'.

Jamie.

--

 

[Jamie Collins]

I found that
using between doesn't account for any value in the FIELD that's a decimal.

The problem could be that you are using a floating point type e.g.
Single or Double which are *approximate* types. Few things in life are
genuinely floating point and I'd suggest exam results need to be
exact. Use an *exact* type such as DECIMAL, which is the native Jet
type for decimal values. Unsurprisingly, DECIMAL works fine with
BETWEEN constructs.

I agree with JohnV that for reasons of maintenance the partitions
('divisions') for your grades should probably be in a table, rather
than hard coded into SQL. However, I suggest that rather than store
just the lower bounds as INTEGER (0, 55, 65, 78, 91) you instead store
both the upper and lower bounds to the appropriate decimal scale to
make you exam scores, again using the DECIMAL type e.g. assuming your
exam results are stored to two decial places: [0.00, 54.99], [55.00,
64.99], [65.00, 77.99], [78.00, 90.99], [91.00, 100.00]. This way, you
don't need a subquery to find the upper bounds and you can use your
(and mine) preference for BETWEEN constructs in SQL e.g.

SELECT GradeLetter
FROM GradeValues
WHERE GradeValues.Course = yourtable.Course
AND yourtable.Score BETWEEN GradeValues.lower_bound AND
GradeValues.upper_bound;

Jamie.

--

 

[Tony Toews [MVP]]

In the OP's case, perhaps the criteria for determining a grade based
on an exam score will change a day, a month or a year later, therefore
storing grade would fit your advice.

In your example, you could retain a 'history' of prices in the 'items'
table and therefore would be able to calculate the cost of an item
retrospectively based on the transaction date rather than store it.

The rule of thumb is to consider the model used (current state tables
or history tables, high insertion) and on performance e.g. is the
calculation faster than a disk/network read, etc. Of course, you can
always change the design or move to another platform... In other
words, it's difficult to make a recommendation based on so little
information, hence the 'rule of thumb' rather than 'hard and fast
rules'.

Good point. Yes, it is possibly that the criteria for a letter grade
could change.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm