formula in calculated field back into table

  • Thread starter Thread starter MTHarris
  • Start date Start date
M

MTHarris

Anyone know a way to get the result from a formula that resides in a
calculated field on a form back in a table. Result will be currency form.
 
It's a bad idea to store calculated fields. You can always recalculate them
as required.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Yes , but another part of what I am storing is percetage numbers that are
calculated from weight input.. and I will have to be able to average and do
deviations on those percentages.

Or maybe there is a better way? I appreciate any further assistance.

MTHarris
 
The reason it is a bad idea to store calculated values is the scenario when
the operands change. Many many records instantly become a maintenance
nightmare. If the user is inputting a weight value, it might be better to
store that, and calculate the other value as needed.

In any case, you can store values in a table using the following code:
Dim sSQL As String

sSQL = "INSERT tblMyTable (Field1, Field2) VALUES (" &
Me.txtNumericControl & ", """ & Me.txtCharacterControl & """)"
CurrentDb.Execute sSQL, dbFailOnError

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
k.. thanks for your help.. One last question if you don't mind. I
understand the storing the weights in a table. The thing I am wondering is
with there being approximately 26 calculated fileds which end up with half
deriving a percent passing.. which is based on the weights input, How can I
do that and then be able to generate a report wich averages and deviates
those percent passing calculated fields?

I apologize for continuing to post but am really glad to have found someone
that has been willing to help me

MTHarris
 
oh yes.. the thing is I am dealing with large groups of samples.. so my
report need to show that percent passing calculated field result by date,
stacked with all samples from a month.. and then do the average and
deviation. I already have a very intense report done that does all that,
but it is dependent on my importing data (percent passing) from an excel
workbook which feeds the query and report based off the query.

MTHarris
 
You can do your calculations, on a row by row basis, in the Detail section
of the report - much like you would on a form. As for the averages and std
deviations, you can also do that in the appropriate report section (perhaps
the Report Footer, or one added below the Detail section).

For the calculations, use the following syntax in the control's RecordSource
property:
=([field1] + [field2]) * [Weight]
....or something similar

For the averages and std deviations...
=Avg([textbox1])
=StDev([Textbox1])
....or something similar

**Check the online Help for the appropriate function (I think they're StDev
and StDevP).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top