Solved: CAPTURE RESULTS OF A FORMULA ON A TABLE

[GaryB]

Hi,

In Access 2000 I have found that if you have a text box with a formula in it that automatically adds up a total (=a1]+[a2]+[a3] etc.. ) the results are not entered on the table. I know that automated formulas are not captured, but I was wondering if there was a work around for this. Any help is, as always, appreciated.

Gary:banghead:

[mattj]

As a rule, you shouldn't store calculated values in a table. If you are already storing the data required to perform the calculation, there is no need. It just takes up unneeded space in the db, and if any of the variable values change, your result is then incorrect.
It should just be recalculated as needed, either in a form, query, or report.

HTH
Matt

[GaryB]

I would agree with what you are saying except in this case there is a variable I need to introduce in the equation that directly effects a report and the only place I can put it is in the calculated field for it to work. So I guess my question is can it or can it not be done?

Gary

[mattj]

Even in the situation you describe, storing the calculated value is still not correct.
There are a host of different methods to store separate variables or constants that are not (or should not) be stored in the same table. These include Global or Public variables (if you want to create and use a function) or using a separate table to store the value.

The only time I have ever seen a valid reason to store a value such as this is to maintain the history of some number, where the variables used are subjec to change.

That being said, yes it can be done - create another text box bound to the field in the table where you wish to store the value. Then use some event to set the value of that text box to equal the text box where you are performing the calc.

My 2? - create and use a function instead and scrap the idea of storing the value. It's rather easy.


Matt

[GaryB]

Thanks Matt,

But now you've got me wondering if there is a better way to do this than I've been thinking. I have a portion of the database isolated to specifically run this problem, but it is 500kb zipped and that's too large to attach here. Our ftp site is ftp.galaxypress.net (ftp://ftp.galaxypress.net) User name is public and the password is public and in the public folder is a folder named galaxy press which has a zipped file of the database in it. If anyone can take a look at this I would appreciate the help. The problem I have is the form press setup into which an adustment % is entered needs to reflect on the report that you will see in the database. What I can't figure out is how to attach the % information to each press listed. The entire database is done with formulas entered into cells, so to speak, and there is no custom coding ( since I don't really know how to code). I need the final report to reflect the adjustment % for each press.
Thanks again,

Gary

[mattj]

This isn't the answer you are looking for, but I feel that it is the best answer. Your current database structure is what is prohibiting you from what should be a simple task.
In your Press table, you have what is called "repeating groups", ie. Press1, press2, ...Press10; AVOC1...AVOC10, etc...
This violates first normal form, and will makes things much more difficult than they need to be. Instead of ech press being a separate column, each press should be a separate row.
I suggest that you put a hold on this project and become familiar with the concepts of normlization.
While this does not address the issue at hand, most experienced developers will tell you that while you can "cover up" most table structure problems by throwing enough code at them,
it is rarely worth the effort, and with time the problems with the application just get progressively harder to fix via code (and said code usually gets pretty messy, as well).
Take a look at these links:
(at the very least, read this one)
http://www.gpcdata.com/downloads/normalizationgpgonaccess.pdf

http://www.datamodel.org/NormalizationRules.html#one
http://support.microsoft.com/kb/283878
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=68&Number=290041
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=68&Number=309586

Matt

[GaryB]

Thanks for you straight forwardness Matt. I do understand normalization and I was looking for a way around the way I had to build this data base. However, what I hadn't realized when I was generting the reports you look at was the adj.% is a constant that applies to all the presses in the same way at the same % and applies only for the inks used. This being the case I was able to use the first adjustment box [a1] and apply it to the totaled outcome and that makes the report valid. Since you didn't get the whole database but only the parts pertaining to the report I was questioning. I can see why you would assume normalization was a problem. You did, however, get me thinking of another way to approach this and that is going to be version 2 of this program. Thanks for the insight and help. I will mark this solved.

Gary

[mattj]

:thumb Glad you were able to solve the issue :thumb