Sum multiple values on a form

[Snowflake68]

I have multiple text boxes on a form that are bound to different costs field in a table. I want to be able to sum all of the cost fields into one Grand Total field without having to explicitly write out each field in a query to total them all up.

Is there a way of using the Tag property to identify each of the text boxes that I want to sum up and then possibly loop through them to sum up the total into another text box that is bound to the same table?

At the moment I have an update query that runs to update the total for 10 of the costs but I have another 50 or so to add so the query is becoming cumbersome to amend.

This is the SQL from my current query

UPDATE tblMain SET tblMain.QuoteTotal = Nz([HeatCost])+Nz([PlaceCost])+Nz([CoverCost])+Nz([Expenses])+Nz([TypeTotal])+Nz([FurnitureTotal])+Nz([LaundryTotal])+Nz([PrintTotal])+Nz([ScreenTotal])+Nz([DeliveryTotal]);

 

[plog]

You need to fix your tables before you go further with forms or anything else.

HeatCost, CoverCost, etc. should not be seperate fields in a table. Instead they should be broken out into a new table where the type is in one field and the amount in another. Like so:

Costs
CostID, MainID, CostType, CostAmount
1, 17, Heat, 36
2, 17, Place, 44
...
The CostID is an autonumber primary key for the cost table. MainID links back to the autonumber primary key in tblMain and tells you what record each cost relates to. With a table like that, your issue becomes trivial.

The same thing applies to all those _Total fields--maybe. They for sure shouldn't seperate fields in a table like they are now, if you need them they should be stored like the Costs I illustrated above. But, if they are calculated from other data in your database, you shouldn't store them at all. If you can sum up other fields to determine what value they have--you don't store that value, instead you create a query and calculate that value when you need it.

Again, focus on your tables and read up on normalization: https://en.wikipedia.org/wiki/Database_normalization

 

[Snowflake68]

You need to fix your tables before you go further with forms or anything else.

HeatCost, CoverCost, etc. should not be seperate fields in a table. Instead they should be broken out into a new table where the type is in one field and the amount in another. Like so:

Costs
CostID, MainID, CostType, CostAmount
1, 17, Heat, 36
2, 17, Place, 44
...
The CostID is an autonumber primary key for the cost table. MainID links back to the autonumber primary key in tblMain and tells you what record each cost relates to. With a table like that, your issue becomes trivial.

The same thing applies to all those _Total fields--maybe. They for sure shouldn't seperate fields in a table like they are now, if you need them they should be stored like the Costs I illustrated above. But, if they are calculated from other data in your database, you shouldn't store them at all. If you can sum up other fields to determine what value they have--you don't store that value, instead you create a query and calculate that value when you need it.

Again, focus on your tables and read up on normalization: https://en.wikipedia.org/wiki/Database_normalization

Thanks, you are correct my table design is rubbish and I have got into bad habits. I will take your advice and sort it out. Thanks