Calculate Fields

[Averil Pretty]

Hi there,

I am fairly new to Access from Excel - I realise that its
a totally different mindset so I'm asking for a little
advise.

I have created a sample database that uses dummy data and
shows what I would like the end result to be but in order
to use real data I need help with some of the backend
calculations so we can move away from Excel.

In Excel I have several sheets with data and calculations
on them. The sheets are Scores, Totals and Report. The
calculations are all performed in the Totals sheet using
the data entered in the Scores sheet. The Report sheet
has several charts that use data from the Totals sheet.

What I need to do in Access is have the scores entered for
each person (say 10 people) in a Scores table - no problem
there. Where I come unstuck is how to calculate the
Totals for each person. The original formulas are quite
indepth IF formulas.

For example, to calculate one cell in Excel the following
formula is used:

=IF(((4*Scores!$C$8+3*Scores!$C$16+1*Scores!$C$20+1*Scores!
$C$21+1*Scores!$C$23+(100-(1*Scores!$C$25)))/1100)
*5.5<=0.2,0.2,((4*Scores!$C$8+3*Scores!$C$16+1*Scores!
$C$20+1*Scores!$C$21+1*Scores!$C$23+(100-(1*Scores!
$C$25)))/1100)*5.5)

I would like the value stored in the Totals table once it
is calculated. I don't know if I should be using
Expressions or what...

SO I have the following tables:

Candidate (ID, name etc.)
Raw Scores (manually entered)
Behaviour Scores (this is where I need the calculations)

And I have the following forms/reports with charts that
use data from the Behaviour Scores table:

Traits Graphs
Behaviour Graphs

From a form called Candidate List a user can click on one
of two buttons that will bring up either of the
forms/reports for that candidate.

Everything in the sample database is working fine because
I have manually entered the Behaviour Scores for each
candidate.

Ideally, I would create a simple data entry form for us to
enter the scores etc and this would have a button on it we
could click that would magically generate the behaviour
scores... I just have no idea how this would be done!

I apologise for the lengthy question but I thought I
should give you all the details.

Any help is much appreciated. Thanks in advnace.

Averil Pretty

If you need to email me you can at (e-mail address removed)

 

[xxx]

Can you please send some sample data that you have in your Scores Spreadsheet? Can't figure out exactly what you are trying to calculate?

 

[Jeff Boyce]

Averil

A scan through this (tablesdbdesign) newsgroup will reveal a strong
consensus against storing calculated values in Access. The three reasons I
recall for not doing this are: 1) against relational design principles, 2)
redundant and unnecessary storage, and 3) slower to retrieve a stored value
than to calculate it in a query.

And my top reason for recommending against storing "totals" as a field is
the extra work it forces you to do -- you'll need to write data
synchronization routines to keep the factors, terms and totals "in sync", in
the event someone changes a value (say, correcting an error).

If you already have a form for the Individuals, perhaps you could create a
(sub-)form for the Individual's scores, and calculate the total (?your
Behavior score) in an unbound control on your main form.

As a matter of 'group protocol, Q & A is to be kept in the group, that all
might benefit.