Multiply Cells on a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a form called aMasterForm which writes to a table called
aMasterTable, on the form exists 3 cells Priority, Proximity and Risk_level

I would like the form to automatically fill in the Risk_Level Cell on the
form and enter the result into the table aMasterTable.

Where Risk_Level is the product of Priority and Proximity

Risk_Level = Priority*Proximity

I have not got a clue where to begin can anyone help…..

Many Thanks in advance

DomNHS
 
DomNHS,

Where to begin is to entirely remove the Risk_Level field from your
aMasterTable table. There is no reason to store this value in the
table, and many reasons not to. Whenever you need the Risk_Level for
your purposes on form or report, you can simply retrieve it by
Priority*Proximity.

As regards your aMasterForm, put an unbound textbox on the form for the
Risk_Level, and in its Control Source property, put...
=[Priority]*[Proximity]
 
Thankyou For your reply.

I have read several messages on this subject and it has been mentioned in
most of them that it is not good practice to do what i am asking, can you
tell me just out of interst why this is?

Steve Schapel said:
DomNHS,

Where to begin is to entirely remove the Risk_Level field from your
aMasterTable table. There is no reason to store this value in the
table, and many reasons not to. Whenever you need the Risk_Level for
your purposes on form or report, you can simply retrieve it by
Priority*Proximity.

As regards your aMasterForm, put an unbound textbox on the form for the
Risk_Level, and in its Control Source property, put...
=[Priority]*[Proximity]

--
Steve Schapel, Microsoft Access MVP

Hi,

I have a form called aMasterForm which writes to a table called
aMasterTable, on the form exists 3 cells Priority, Proximity and Risk_level

I would like the form to automatically fill in the Risk_Level Cell on the
form and enter the result into the table aMasterTable.

Where Risk_Level is the product of Priority and Proximity

Risk_Level = Priority*Proximity

I have not got a clue where to begin can anyone help…..

Many Thanks in advance

DomNHS
Click to expand...
Click to expand...
 
The best way to ensure that the calculations are correct is to do them 'on
the fly' (the way Steve has suggested). If you start storing the
calculations into tables you have to ensure that the data is up-to-date
which can be easily overlooked especially if more than 1 form or query is
changing the information in the table (Procedures written in code can update
the information so you would also need to ensure the data was re-calculated
here as well). I would say that this reason alone is enough of an argument
to not store calculated values into tables but another advantage is that you
are not storing this information so the database will not be as large and
will probably run a lot quicker as well. There are probably other reasons
for not storing data in this way but i would say these are the 2 main
reasons.

HTH,

Neil.

DOMNHS said:
Thankyou For your reply.

I have read several messages on this subject and it has been mentioned in
most of them that it is not good practice to do what i am asking, can you
tell me just out of interst why this is?

Steve Schapel said:
DomNHS,

Where to begin is to entirely remove the Risk_Level field from your
aMasterTable table. There is no reason to store this value in the
table, and many reasons not to. Whenever you need the Risk_Level for
your purposes on form or report, you can simply retrieve it by
Priority*Proximity.

As regards your aMasterForm, put an unbound textbox on the form for the
Risk_Level, and in its Control Source property, put...
=[Priority]*[Proximity]

--
Steve Schapel, Microsoft Access MVP

Hi,

I have a form called aMasterForm which writes to a table called
aMasterTable, on the form exists 3 cells Priority, Proximity and
Risk_level

I would like the form to automatically fill in the Risk_Level Cell on
the
form and enter the result into the table aMasterTable.

Where Risk_Level is the product of Priority and Proximity

Risk_Level = Priority*Proximity

I have not got a clue where to begin can anyone help...

Many Thanks in advance

DomNHS
Click to expand...
Click to expand...
Click to expand...
 
DomNHS,

The points Neil has made are excellent ones.

In database design there are certain principles. One of them, which is
sometimes referred to as atomicity, and was apparently invented by John
Vinson's grandmother, is the idea of one place for everything and
everything in its place. In the end, it is normally more difficult and
more complicated if the same information is stored more than once in a
database. In effect, this is what saving a calculated value amounts
to... putting the same information in more than one place. It is the
kind of thing that is appropriate in spreadsheets and stuff like that,
but not in a database.
 
You don't say in your original post whether the Priority and Proximity
information is stored in the table. If so, then as you have been advised,
the Risk_Level should be computed on the fly. If this is a data input form
however, and Priority and Proximity are entered by the user to calculate the
stored data, then you certainly may store the calculation in the table.

LGC
 
All,

Thanks to all of you for your responses, I have to say I find these
Newsgroups the best way to learn and develop skills in software and have
found them particularly useful for MS Access.

Kind Regards

DomNHS
 
Back
Top