Automatically calculate a field in a table 1

[epruittjr]

I have an Access database that has several currency fields in my table. I would like to have one of the fields automatically calculate the total of several fields. I have tried entering a formula using the Sum function but Access will not recognize my field names.

I'm new to Access and maybe it's not possible to put a formula in a table field to automatically calculate the total of other fields. Any help would be greatly appreciated.

 

[jebry]

Hi!

You won't need to store the total since it can be calculated any time you need it. If you want it viewable in a table type window, just build a query with the total formula as one of the fields and run that query for viewing.

hth
Jeff Bridgham

 

[epruittjr]

Thanks Jeff,

I know I can get the total using the query but I was trying to cut down on any errors in total that are stored in the table. A couple of times the total cost field has had the wrong total entered and resulted in incorrect totals posted in the reports.

Do you know if it is possible to have Access enter the total cost in a table via a formula? If not I can use a macro to check the total cost field for correct entry.

 

[jebry]

Hi again!

If they are entering in the data by form, you can set the total field yourself through code in the BeforeUpdate event procedure. Have a text box with its control source set to the total field and use the following code:

txtTotal = txt1stField + txt2ndField + etc.

The total will automatically be stored.

But the main thrust of my previous post was that you should not be storing the total at all. Any data which can be totally(if you will excuse the pun) defined by data already existing in the database shouldn't be stored, it should be calculated whenever it is needed.

hth
Jeff Bridgham
P.S. If you still want to store the total and need more help, let me know.

 

[epruittjr]

Thanks again Jeff. You've been very helpful. Looks like I really need to get into finding out more about Access.