Calculate total in table

[davidjearly]

Hi,

I have a table with 4 different columns. Each column has a measurable indicator where the value can range from 1 to 10.

In the last column, I would like the field to calculate the average score for the previous 3 columns in that row.

Is there a way to do this?

Thanks,

David

(Using Access 2007 on Win XP SP3)

 

[DCrake]

You can do this using a query

 

[davidjearly]

Ok, but how would I get the value from that query back into the table so that it can be displayed on a form?

Thanks.

 

[neileg]

You don't. As a general rule you don't store calculated values. Base the form on the query instead of the table.

 

[davidjearly]

You don't. As a general rule you don't store calculated values. Base the form on the query instead of the table.

I can't as the rest of the form has to be based on the table.

Is it possible then to calculate an average total in a form, based on values in other textboxes on that same form? This average total does not have to be stored.

Thanks again.

 

[jsv2002]

Why does the form have to be based on the table? If your query is based on the table in question you can include all the fields you require for your form in it (in fact all of the fields in your table if required)

You could do the calculation on the form through VBA and an unbound control but this in itself would/may cause problems when other fields are updated or if required to print the document to a report at a later date.

Result being there is nor reason you cannot do your calculated fields in a query and I would say 99% of the forms I create are based on queries as they are more versatile and other information can be drawn from related tables if required.

good luck John

 

[The_Doc_Man]

I can't as the rest of the form has to be based on the table

OK, here's what you do.

1. Print this answer out.

2. Wrap the print-out around a 2x4 about 3 feet long.

3. Use it to pummel the person who gave you such a stupid requirement.

Now, let's be more productive.

If you base the form on the table, don't compute the result in a query.

Compute the average in a computed field on the form, one that is not bound to a field in the table. Instead of having the field name in the form's recordsource for that control, have a formula that does your computation.

Same concept would work for a report.

The ONLY time this won't work is when you open the table in TABLE view because a computed field should never be stored in a table unless there is an overwhelming reason to do so. Without knowing the reason in your case, we are not overwhelmed.

OK, here's the TECHNICAL reason why you don't do that.

When you store a computed value, it violates one of the normalization rules. See, the table has a prime key. The current values in the table depend on the prime key (i.e. different PK points to different values). But the AVERAGE of those three fields doesn't (technically) depend on the PK at all. It depends on something other than the PK. The fact that it is a transitive dependence (average depends on fields, fields depend on PK) is not a valid excuse. That average violates normalization rules based on a key-dependency rule. The same rule can be stated in other ways that obscure the key dependency a little, but that is why you don't store computed values unless there is a humongous reason to do so.

 

[davidjearly]

Compute the average in a computed field on the form, one that is not bound to a field in the table. Instead of having the field name in the form's recordsource for that control, have a formula that does your computation.

Thanks for your reply.

Can you tell me how to do that?

I have tried this:

=Sum([Field 1]+[Field 2])

But it isn't working. If 'Field 1' on my form contains the value '1' and 'Field 2' contains the value '3', the above expression produces the answer '13', rather than '4'.

Help!

 

[Scooterbug]

Thanks for your reply.

Can you tell me how to do that?

I have tried this:

=Sum([Field 1]+[Field 2])

But it isn't working. If 'Field 1' on my form contains the value '1' and 'Field 2' contains the value '3', the above expression produces the answer '13', rather than '4'.

Help!

What data type(s) are Field1 and Field2? My guess is that it's text, which is probably where the problem lies. Change them to a number type.

 

[neileg]

When you do calculations in a form you use the control names not the field names.

However, you should definitely be basing your form on a query not the table. There are lots of advantages in using a query and no disadvantages.

 

[The_Doc_Man]

Sum as a function would work, but why bother?

=[Field1]+[Field2]+[Field3]

with the understanding that if you are using the controls, they are in a text format, so you might instead use this, assuming the numbers are really SINGLEs. But you can do this for other data types too.

=CSng([Ctrl1])+CSng([Ctrl2])+CSng([Ctrl3])

For long integers, use CLng. For shorter integers, use CInt. For DOUBLES, use CDbl.

Just remember, if it is set up for display, it is probably text and text cannot be added that way.

 

[davidjearly]

Thanks Doc-Man and others. I took the steps suggested previously and redesigned that particular form on a query so now have the average calculating as it should.

Next problem I have encountered is with this function below on a different form.

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

On this form, there is a text box called 'DOB' which references a column in a table. Below the DOB box, I have another text box called 'Age' which has the above function set in the 'control source' field. It is meant to return the age of the individual based on the date of birth entered above.

It will do that, but it takes some time, often needing the database to be closed and re-opened before the age is displayed. Is there any way to make it perform this calculation instantly? At the moment, the calculated age is not stored in any table and I don't want it to be. Would just like it to be displayed on the form when viewing a record.

Thanks!

 

[Insane_ai]

Next problem I have encountered is with this function below on a different form.

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

On this form, there is a text box called 'DOB' which references a column in a table. Below the DOB box, I have another text box called 'Age' which has the above function set in the 'control source' field. It is meant to return the age of the individual based on the date of birth entered above.

It will do that, but it takes some time, often needing the database to be closed and re-opened before the age is displayed. Is there any way to make it perform this calculation instantly? At the moment, the calculated age is not stored in any table and I don't want it to be. Would just like it to be displayed on the form when viewing a record.

Thanks!

Paste your code into the control source property of the text box on your form.

 

[The_Doc_Man]

When something takes a close and re-open to make it work, that indicates that something else in the same form or table needs a refresh. Look up Refresh in Access help. Other option: Requery might also be required, or required instead of refresh. Hard to tell from here.