How to sum fields and update a table using that info??

[Bishrul]

Scenario : i have a table
tblOne - SCID , BoxSetNo , BoxesInBoxSet
--------------1----------1------------5
--------------1----------2------------7
--------------1----------3------------9
--------------2----------1------------3
--------------2----------2------------7

so what i want is , in a form i need to have a textbox where the sum of SCID 1 should display(in this case 21), so i can do more calculations with that value
form's record source is a different table!!
i can add the field from other tables, but it only shows one record (SCID 1 and the value is 5 not 21) not the sum of it

pls how to calculate the sum of a field in a textbox??

[cactusdata]

Use this expression as control source for your textbox:

=DSum("BoxesInBoxSet","tblOne","SCID = " & [SCID] & "")

[NeoPa]

Generally speaking those who work with databases understand, to a greater or lesser extent, the concepts of Normalisation (See Database Normalisation and Table Structures). As such, we would very rarely recommend updating tables to reflect calculated values.

Immediately you have data that is already stale. Sometimes, very rarely in my experience, it is necessary, or at least helpful/easier, to create data in a table temporarily that can be used - but not stored long-term.

There are many ways to get totals into a Control on a Form. CactusData has illustrated one. Another very simple one is to ensure there is a calculated field in the SQL used to source your Form. Aggregating the source data typically ensures the dataset isn't updatable though so that should always be taken into consideration.

Appending data into a temporary table can be done with an Append query based on an aggregated SELECT section. I'll go into more detail on that if required but please understand I am not recommending this approach. In fact, my advice is to avoid it like the plague if you have any alternative.

[Bishrul]

Thanks alot Guys!!
Helpful Tips Saved Me