Adding 4 fields to make a total which determines another field?

[Richard Horne]

Hi guys.

I have a table in my database called SupplierCheck. This table contains
amongst others, various fields called Quality, Price, Delivery,
Communication, Consistency, Service and Reliability. Each of these fields can
only contain a value of either 1 2 3 or 4. The idea is that overall score of
all these fields combined determines the status of a supplier in our database.

So a few questions.

Should I be storing the summed total in the actual table or simply
calculating on the fly on a form? My head tells me this should be done on a
form, but the actual overall score determines a rank that is applied to each
supplier.

I.e.

0-16 = C
17-28 = B

This needs to be saved against each supplier as my purchase order system
needs to do different things depending on whether the supplier is ranked B or
C?

So can anyone tell me the best way of doing this?

 

[KARL DEWEY]

Should I be storing the summed total in the actual table or simply
calculating on the fly on a form?
You should not store it.

IIF([Quality]+[Price]+[Delivery]+[Communication]+[Consistency]+[Service]+[Reliability] > 17, "C", "B")

 

[Douglas J Steele]

Or, if any of the fields can be Null,

IIF(Nz([Quality],0)+Nz([Price],0)+Nz([Delivery],0)+Nz([Communication],0)+Nz(
[Consistency],0)+Nz([Service],0)+Nz([Reliability],0) > 17, "C", "B")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

calculating on the fly on a form?
You should not store it.

IIF([Quality]+[Price]+[Delivery]+[Communication]+[Consistency]+[Service]+[Re
liability] > 17, "C", "B")

 

[Richard Horne]

Hi guys, thanks for that works great.

I'd now like to complicate things by adding another IIF to the equation.

In the same table is another field, a Yes/No tick box. If this is equal to
Yes I want the formula below to calculate the value, but if this box is equal
to No, I want the field in the formula below to equal X.

Can that be done?

Or, if any of the fields can be Null,

IIF(Nz([Quality],0)+Nz([Price],0)+Nz([Delivery],0)+Nz([Communication],0)+Nz(
[Consistency],0)+Nz([Service],0)+Nz([Reliability],0) > 17, "C", "B")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

calculating on the fly on a form?
You should not store it.

IIF([Quality]+[Price]+[Delivery]+[Communication]+[Consistency]+[Service]+[Re
liability] > 17, "C", "B")

 

[Douglas J Steele]

IIf(Nz(Me.MyTickBox, False),
IIF(Nz([Quality],0)+Nz([Price],0)+Nz([Delivery],0)+Nz([Communication],0)+Nz(
[Consistency],0)+Nz([Service],0)+Nz([Reliability],0) > 17, "C", "B"), "X")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi guys, thanks for that works great.

I'd now like to complicate things by adding another IIF to the equation.

In the same table is another field, a Yes/No tick box. If this is equal to
Yes I want the formula below to calculate the value, but if this box is equal
to No, I want the field in the formula below to equal X.

Can that be done?

Or, if any of the fields can be Null,

IIF(Nz([Quality],0)+Nz([Price],0)+Nz([Delivery],0)+Nz([Communication],0)+Nz(
[Consistency],0)+Nz([Service],0)+Nz([Reliability],0) > 17, "C", "B")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Should I be storing the summed total in the actual table or simply
calculating on the fly on a form?
You should not store it.

IIF([Quality]+[Price]+[Delivery]+[Communication]+[Consistency]+[Service]+[Re
liability] > 17, "C", "B")

:

Hi guys.

I have a table in my database called SupplierCheck. This table contains
amongst others, various fields called Quality, Price, Delivery,
Communication, Consistency, Service and Reliability. Each of these fields can
only contain a value of either 1 2 3 or 4. The idea is that overall score of
all these fields combined determines the status of a supplier in our

database.

So a few questions.

Should I be storing the summed total in the actual table or simply
calculating on the fly on a form? My head tells me this should be

done
on a

form, but the actual overall score determines a rank that is applied

to
each

supplier.

I.e.

0-16 = C
17-28 = B

This needs to be saved against each supplier as my purchase order system
needs to do different things depending on whether the supplier is

ranked
B or

C?

So can anyone tell me the best way of doing this?

 

[Richard Horne]

Thanks for your help guys, I've finally got this working exactly how I want
it to by using the formula:

=IIf([ISO]="-1","A",IIf([ToScore]="0","X",IIf([Total_Score]>"17","B","C")))

The final problem I now need to solve is thus:

The above formula is saved against a field pulled from the same table,
called Rating. The formula above puts either an A, B, C or X in the field
called Rating depending on the results of the other fields, I now want this
rating to be saved against each record so I can then query the Rating in
another form without having to include all the scoring fields, but the
problem is, the letter in Rating is not being saved.

How can I get the rating to save against each record?

 

[John Vinson]

I now want this
rating to be saved against each record so I can then query the Rating in
another form without having to include all the scoring fields, but the
problem is, the letter in Rating is not being saved.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

It's no big deal to include the scoring fields in the Query; you don't
need to display them individually, or even include them in the query
grid.

John W. Vinson[MVP]

 

[Richard Horne]

Hi John and thanks for your reply.

I have since realised exactly what you said and simply displayed the scoring
fields on my other form but made them invisible and calculated the total on
the fly.

Thanks for your help on this one guys. As always it's very much appreciated.