By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,069 Members | 1,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,069 IT Pros & Developers. It's quick & easy.

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

P: 7
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??
3 Weeks Ago #1
Share this Question
Share on Google+
3 Replies


cactusdata
Expert 100+
P: 102
Use this expression as control source for your textbox:

Expand|Select|Wrap|Line Numbers
  1. =DSum("BoxesInBoxSet","tblOne","SCID = " & [SCID] & "")
3 Weeks Ago #2

NeoPa
Expert Mod 15k+
P: 31,761
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.
3 Weeks Ago #3

P: 7
Thanks alot Guys!!
Helpful Tips Saved Me
3 Weeks Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.