Sum Row Query

[benkingery]

I have a table that stores individual costs associated with products in my database, and then also stores the sum of those costs in a "Finished Cost" column. I do need to store the "Finished Cost" data because we use it all over the place. I'm wondering what is the best way to automatically sum the rows so this field is automatically populated. I have an Update Query that works and goes something like this:

UPDATE Parent_Table SET Parent_Table.Fin_Cost = [Parent_Table].[Cost1]+[Parent_Table].[Cost2]+[Parent_Table].[Cost3]+[Parent_Table].[Cost4];

Like I said, this works, but it performs this query for all records within the database, so it takes a little while to perform.

I have added a control to the form that interacts with this table that when clicked activates a RunSQL macro that contains the above SQL statement. As you can imagine, it takes a while for the whole thing to run because it has to update the whole table.

Does anyone have any idea on how to sum up these 4 costs into the "Finished Cost" column and then store that sum into the table?

 

[maxmangion]

Hi,

In my opinion you have two problems there, first of all you should not be storing calculated fields in a table ... do that at query level.

Furthermore, search this forum for normalization, because from the field names you described it seems clear that your table setup is not normalized.

 

[benkingery]

Thanks for your reply. I'm no database guru, but I'm pretty sure my tables are in the 3rd normal form. What problems do you see specifically with what I described?

Second, I understand that as a general rule you don't want to store calculated fields such as age, etc. in a table. However, the input valus are static and do not change and for purposes of this Database and the business rules that are required, I do need to store that information. There are times when I need to pull together and generate reports that include this "Finished Cost" calculated field.

Can you give me any advice?