Query calculated field question

[Victoria]

hello

I have a query with fields ID, Level, a1, a2, a3, a4,......... a40.

The fields a1 to a40 will either be blank or will have an integer value
from 1 to 5. I'd like to add a calculated field, called Score, that will
give me the sum of these 40 columns divided by (5 times the number of fields
that have values). How should I write this calculated field.

b) The 40 fields mentioned above have textboxes on a form. Is it generally
better to just do the above calculation for Score on the form, or get the
calculated value of Score from the query?

 

[Allen Browne]

Victoria, can I suggest you take a different approach here?

What you have designed would be fine as a spreadsheet, but it is not right
for Access. In a relational database, you don't put lots of fields in the
one table to hold similar values: you put lots of *records* in a *related*
table.

Whatever ID and Level are, it seems that you could have up to 40 values to
store for them. Drop the fields a1 to a40 from this table. Instead, use
another table with these fields:
ID tells which ID of your existing table this record is
for
TheValue a Number field to hold the score for this record.
If there is actually some difference between the columns (e.g. if a1 is for
the first week or something), you may need one more field that tells which
is which (e.g. it might hold the numbers 1 to 40.)

Now you can create a query that uses the 2 tables, and see the values in a
column (instead of side by side.) That makes it dead easy to get the
calculation you want. Just use a Total query (depress the Total button on
the toolbar), and enter an expression like this into the field row:
Sum([TheValue]) / 5 * Count([TheValue])
or perhaps:
Average([TheValue]) / 5

This concept of a related table that holds many records is really important
in Access.

 

[John W. Vinson]

hello

I have a query with fields ID, Level, a1, a2, a3, a4,......... a40.

The fields a1 to a40 will either be blank or will have an integer value
from 1 to 5. I'd like to add a calculated field, called Score, that will
give me the sum of these 40 columns divided by (5 times the number of fields
that have values). How should I write this calculated field.

b) The 40 fields mentioned above have textboxes on a form. Is it generally
better to just do the above calculation for Score on the form, or get the
calculated value of Score from the query?

This might be too big an expression to work, but you could try

(NZ(a1)+NZ(a2)+NZ(a3)+ <etc etc>) / 5*(40 + IsNull([a1]) + IsNull([a2]) +
IsNull([a3]) + <etc etc>)

If that pops an error you may need to write a custom VBA function to return
this value.

If these are fields in a TABLE then your table structure needs
reconsideration. These should be rows, not fields; a Crosstab query based on a
tall-thin normalized table will let you calculate this average.