Question How to do calculation with columns? (1 Viewer)

[ppataki]

Dear All,

HAPPY NEW YEAR!!!!

I have a database that has numerous fields
I would like to create a new field that displays for example how many given values are in each record

eg
Field1 Field2 Field3 Field4 New field
k x x z 2

"New field" is 2 as there are two "x" values, one in Field2 and one in Field3

What shall be done please in order to get that New field created with the help of a query for example?

Many thanks in advance

 

[John Big Booty]

It's is generally considered bad design to store calculated values in Access.

On your form put an unbound text box and in it's Source Data put;

=Me.Field1 * Me.Field2 * Me.Field3 * Me.Field4

 

[olorin]

If you want to see calculated values based on database fields, you should create an unbound TextBox on the form as John suggests, or create a report based on the fields with an unbound control which does the calculations when the report opens.
Calculated fields in tables are a big "no-no" in normalized databases.
Search for "Normalization" and you will see many reasons why this is.