Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can Calculated Fields be Stored in a Table? - HELP! 1
- Thread starter csunsun
- Start date
-
1
- #2
Hi,
Your calculated value can be stored in a table. If your field which will store the info exists and its part of the table supplying info to the form then put this bit of code in your form:
Me!StoreCalcFieldName = Me!CalcValueTextBox
If it's not then you should create a field that will store the info in your table and then insert it via code. You can run an append query that will insert the code if the field is in another table.
HTH,
jbehrne
If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations
Your calculated value can be stored in a table. If your field which will store the info exists and its part of the table supplying info to the form then put this bit of code in your form:
Me!StoreCalcFieldName = Me!CalcValueTextBox
If it's not then you should create a field that will store the info in your table and then insert it via code. You can run an append query that will insert the code if the field is in another table.
HTH,
jbehrne
If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations
- Thread starter
- #3
Hi!
The next question would be, why do you want to store this data? It is usually best not to store calculated data since the same calculation can be made in a query or directly on a report if the information is needed somewhere other then on the form. This is considered better because the calculated value will be dynamic instead of static. If you need a snapshot of a specific time, then you should make a separate 'history table' where you can store the data you need without interfering with the production data.
hth
Jeff Bridgham
bridgham@purdue.edu
The next question would be, why do you want to store this data? It is usually best not to store calculated data since the same calculation can be made in a query or directly on a report if the information is needed somewhere other then on the form. This is considered better because the calculated value will be dynamic instead of static. If you need a snapshot of a specific time, then you should make a separate 'history table' where you can store the data you need without interfering with the production data.
hth
Jeff Bridgham
bridgham@purdue.edu
Hi,
Although I agree with Jeff, I don't know your situation... When should the code be run? You should run it in your caluculated field's "After Update" event. You shouldn't need brackets around the field names (unless you left spaces in the field names, or you have funny characters in the field name, ex: #,$,^, etc.) However, if you are worried about it try this:
Me![StoreCalcFieldName] = Me![CalcValueTextBox]
- Again make sure you change the names to match your database.
- If you are saving to another table then let me know and I'll post a query to help you out.
jbehrne
If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations
Although I agree with Jeff, I don't know your situation... When should the code be run? You should run it in your caluculated field's "After Update" event. You shouldn't need brackets around the field names (unless you left spaces in the field names, or you have funny characters in the field name, ex: #,$,^, etc.) However, if you are worried about it try this:
Me![StoreCalcFieldName] = Me![CalcValueTextBox]
- Again make sure you change the names to match your database.
- If you are saving to another table then let me know and I'll post a query to help you out.
jbehrne
If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations
I've gotten confilicting advice on precisely this question. A developer who I trust put it this way, (paraphrasing).
"Sometimes it's a matter of which is worse: Hogging up some storage space or bogging down you processor."
Maybe if it's iterative, you store it, if it's simple a*b, you calc on the fly? Maybe look at how many concurrent users there are -- what are you shortest of...space or processor capacity?
Let the record show that I'm NOT a professional developer. I'm a power user. You'll find my stuff is never fully 100% denormalized. If any pros out there fundamentally disagree with my take on things, please speak up.
-->Just because you can, doesn't mean you should.
"Sometimes it's a matter of which is worse: Hogging up some storage space or bogging down you processor."
Maybe if it's iterative, you store it, if it's simple a*b, you calc on the fly? Maybe look at how many concurrent users there are -- what are you shortest of...space or processor capacity?
Let the record show that I'm NOT a professional developer. I'm a power user. You'll find my stuff is never fully 100% denormalized. If any pros out there fundamentally disagree with my take on things, please speak up.
-->Just because you can, doesn't mean you should.
Hi!
Rarely is it advantageous to denormalize production data. I realize that there are times that processing can be time consuming and in those situations I normally (pardon the pun) prefer to set up a reporting database that is updated nightly or weekly depending on business needs and run reports and queries against that. The reporting database can be as denormalized as you like and the processing to update it can be done overnight or over the weekend.
hth
Jeff Bridgham
bridgham@purdue.edu
Rarely is it advantageous to denormalize production data. I realize that there are times that processing can be time consuming and in those situations I normally (pardon the pun) prefer to set up a reporting database that is updated nightly or weekly depending on business needs and run reports and queries against that. The reporting database can be as denormalized as you like and the processing to update it can be done overnight or over the weekend.
hth
Jeff Bridgham
bridgham@purdue.edu
- Thread starter
- #8
thanks for all of the replies!!!
i wanted to store the calculated field from the form, because the reports do not need a bunch of the fields needed for the long calculation.
or maybe i can add all the fields and not show them in the report. i thought storing the field would be easiest.
anyway, i will re-evaluate!
thanks again everyone!
i wanted to store the calculated field from the form, because the reports do not need a bunch of the fields needed for the long calculation.
or maybe i can add all the fields and not show them in the report. i thought storing the field would be easiest.
anyway, i will re-evaluate!
thanks again everyone!
- Status
Similar threads
- Locked
- Question
