field in table based on other fields in same table

[wrbodine]

Hi,

Is it possible to have a field in an access 2000 table that is automatically calculated based on other fields in the same table? Like, taking 3 number fields and having a 4th field in the table be the average of those 3 numbers? If so, how does one go about this?

Many thanks,
Ray

 

[mdthornton]

The way I normally do this sort of thing via a query. In your example, the query (qX say) would just do:

select A, B, C, average=(A+B+C)/3
from X

Now where you previously referenced X in other queries/reports etc use qX instead. This has the benefit, esp for large tables, of saving space also.
Best Regards,
Mike

 

[wrbodine]

Thanx Mike!

Do you know if its possible to have this average field in the table itself? It sounds like it might not be....

 

[mdthornton]

At the table level, I don't know of any mechanism that would automatically reflect changes in the source columns. It is possible in other environments (eg SYBASE) but not in Access.

Of course, if you only interact with the table through a form you can code up events to update the average column when the others change.
Best Regards,
Mike