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

R

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? :)
 
K

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")
 
D

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!)


KARL DEWEY said:
calculating on the fly on a form?
You should not store it.
IIF([Quality]+[Price]+[Delivery]+[Communication]+[Consistency]+[Service]+[Re
liability] > 17, "C", "B")
 
R

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?

Douglas J Steele said:
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!)


KARL DEWEY said:
calculating on the fly on a form?
You should not store it.
IIF([Quality]+[Price]+[Delivery]+[Communication]+[Consistency]+[Service]+[Re
liability] > 17, "C", "B")
 
D

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!)


Richard Horne said:
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?

Douglas J Steele said:
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!)


KARL DEWEY said:
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? :)
 
R

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?
 
J

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]
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top