Calculated form data not populating table

[mkb69]

Hi all ... been awhile since I have had to create a database so I have gotten a little rusty.
I have a form where some of the fields I have formulated to calculate an amount. Example... =[GrossAmt]*[FeePercent] This is calculating into the form correctly but not writing to my table for that field. What am I doing wrong or missing here?

 

[pbaldy]

You're trying to store a calculated value, which is rarely a good idea. Just calculate it when you need it.

 

[ScottGem]

Hi all ... been awhile since I have had to create a database so I have gotten a little rusty.
I have a form where some of the fields I have formulated to calculate an amount. Example... =[GrossAmt]*[FeePercent] This is calculating into the form correctly but not writing to my table for that field. What am I doing wrong or missing here?

What you are missing is that, as a general rule, we don't store calculated values. This violates normalization rules. Calculations are done for display where needed. You use the same calc you currently have on your form whereever you need the result to display.

Also you don't have fields on a form. You have controls that may or may not be bound to a field in a table. This is a subtle but important distinction.

 

[mkb69]

Thanks for the responses and I have convinced my boss I do not need to store if I calculate it to a query or report. I also had a revelation last night that once all data is entered I could run a "make table" to store the calculated fields. At some point I do want to store it (even though as a general rule you don't) due to the nature of our work for archive info once the database is no longer useful.

 

[ScottGem]

Thanks for the responses and I have convinced my boss I do not need to store if I calculate it to a query or report. I also had a revelation last night that once all data is entered I could run a "make table" to store the calculated fields. At some point I do want to store it (even though as a general rule you don't) due to the nature of our work for archive info once the database is no longer useful.

No you don't. You store (or archive) the components of the calculations, NOT the results.

 

[The_Doc_Man]

Scott is right as usual. You may share this response with your boss if you wish. And if your boss has questions, go ahead & post them.

Rule #1: There are no "hard and fast" rules in programming. (not even this one... )

The business about storing data you could compute is a subtle one having to do with normalization. It applies to sales, stocks, inventory, employment history, taxes, etc.

General rule: When archiving, distinguish among the fields that can be computed from what you intended to store and the fields that are "basic" elements of your data sets. Don't store the computable items.

Exception: When archiving SUMMARIES of things for historical research but you are not storing the underlying elements, it is OK to store (date-tagged) computed fields as though they were "basic" elements. In other words, if you are NOT storing the items you need to recompute the historical info, then it is OK to at that time compute what you DO want to store and dump the stuff made obsolete by that computation.

Why? Because your computer, if less than three years old, is probably at least a 1 GHz machine. Now 1 GHz = (approximately) 250 million instructions per second based on most Intel instructions taking 3-5 clock cycles, often less. Your disk spins at (approximately) 3600 to 10,000 rpm, or 60 to 166 rps. At 10,000 rpm this is 1.5 MILLION instructions per revolution of the disk. Closer to 5 MILLION for slower disks. Your machine will have PLENTY of time to recompute everything you need to recompute while waiting for the disk to spin just once.

So maybe you've got room on the disk to keep a little bit more, but why be a wastrel? Access does have a storage limit. Why push the envelope? Just store what you think you need to store in order to recompute anything for which a formula exists.

Before I go into a discussion of why this might violate normalization rules, I would merely suggest that you search the forum for questions on Inventory issues because, though the topics may differ, the same exact question (keep vs. recompute) is a frequent flyer here. I know I have explained the concept in detail before. So have others like GHudson, Pat Hartman, ColinEssex, and others too numerous to remember them all.