We help IT Professionals succeed at work.

Storing data from a calculated field into a table.

jcuzzola
jcuzzola asked
on
I have a form name frm_BrowseAdd with a txtbox named % Viable the value in the field was originally static but I want to store it in my table:  tbl_U2_Spinners ! UserVar07.
% Viable is calculted from 2 txt boxes a the form. txtViable and txtTotal.

% Viable  = [TxtViable]/[TxtTotal]*100.

TxtViable and TxtTotal are stored in tbl_U2_Spinners. How could I create a procedure mabey an after update that would store this value in my table. Or calculate it from the data that already exists in my table?
It would be good if I can back calculate all of the existing data..


Can anyone help?

JCz
Comment
Watch Question

BRONZE EXPERT
Top Expert 2016

Commented:
first, it is not advisable to store calculated values in the table. ( you can always get the values using queries)

BRONZE EXPERT
Top Expert 2016
Commented:
select  ([TxtViable]/[TxtTotal]) * 100 as PercentViable
from  tbl_U2_Spinners

Author

Commented:
I know this however,  I am trying to obtain this value in a query and I cannot get accurate output using the last comand in the QBE builder. I think I can store this value and then pull it into a report much easier.
BRONZE EXPERT

Commented:
I agree with Capricorn1.  Storing calculated data in the record will lead to problems if there's a data entry error and the elements of the equation are changed.  There's nothing in Access to force the calculated field to re-calculate.

It's much easier, and more efficient, to create a query for your forms and reports, and add the calculation as a new query column and display it dynmacally.  If any of the data should change, the 'calculated column' will instantly change also.

You can still use this 'calculated column' as a field on a report or form and 'sum' it, just like any other recordsource field, etc.

Scott C
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
Won't 'back-calculating' the existing data present you with the same inaccuracy problem?
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
As for putting your calculated value into your table (the above comments notwithstanding), is your field in the record source for your form?

If so, it's just a case of doing the following in the form's before update event handler:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    [UserVar07] = [%Viable]
End Sub

Open in new window

Data Analyst
BRONZE EXPERT
Commented:
Otherwise, you could do it via the form's after update event handler - but you need to identifiy which record to update:
Private Sub Form_AfterUpdate()
    CurrentDb.Execute "UPDATE tbl_U2_Spinners " & _
                      "SET UserVar07 = " & [%Viable] & " " & _
                      "WHERE YourIdField = " & txtYourIdControl, _
                      dbFailOnError
End Sub

Open in new window

Author

Commented:
Just got back from lunch I will try it but I may need it to loop. I will fool witj it now....

Author

Commented:
This is what I have there are syntax errors Help please

Private Sub cmd_Via_Click()

''The piece below is from EE

CurrentDb.Execute "UPDATE tbl_U2_Spinners" & "SET UserVar07 =  Me.Viable" & "WHERE Viable  =  Me.TxtViable/Me.TxtTotal * 100,"


 End Sub
Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012
Commented:
First thing I noticed:
You do not appear to have a spaces between ...Spinners"  and  "SET...
... and ...Viable"  and "Where..."

Try
CurrentDb.Execute "UPDATE tbl_U2_Spinners " & "SET UserVar07 =  Me.Viable " & "WHERE Viable  =  Me.TxtViable/Me.TxtTotal * 100,"

Also, why is there a comma after 100?

JeffCoachman
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
Looks to me like you've got some confusion between the FIELDS in tbl_US_Spinners and the CONTROLS on your form.  It's always a good idea to name a CONTROL differently from the FIELD to which it is bound precisely for this reason.

Perhaps you could clarify which of the items you've mentioned are CONTROLS on your form and which are FIELDS from tbl_US_Spinners - you won't get the update query working until you get this right!
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
I can't tell you for sure without the above information, but the query you posted in ID: 20745857 is missing spaces (as pointed out by boag2000) but also ends with a trailing comma (NOT pointed out by boag2000).

You've also missed off the dbFailOnError parameter - without the Access just ignores problems, which can be really confusing when bebugging.

Just for future reference, cut/paste aviods this sort of problem.
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
Sorry, I meant to say that the missing spaces/trailing comma are DEFINITELY a problem, but I can't give you the final corrected SQL without knowing the names of your fields and controls!
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
Your SQL should also be built up by EVALUATING the relevant text boxes and concatenating the result, not REFERENCING the text boxes directly within the SQL.  Again I can't correct this without knowing your field/control names.

I don't think your WHERE condition is right either - you should be updating ONE tbl_US_Spinners record only, not ALL records that have a matching Viable percentage.  
Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012

Commented:
JezWalters,

How was your weekend?
:)


...now back to business...
<but also ends with a trailing comma (NOT pointed out by boag2000).>
Hey Jez, I did point it out:
<20748528>
<Also, why is there a comma after 100?>
:)

My theory was that if they at least cleaned up the obvious stuff, they could then take your design advice and apply it.
:)

JeffCoachman
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
By the way does changing your Viable field to use the 'Double' data type help the accuracy problem any?  You could have a go using the 'Decimal' data type, with a suitably large Scale value too.
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
My most humble apologies Jeff - you can't see the wood for the trees sometimes!  :-)
Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012

Commented:
No problem.
;)
Good luck with this Q, I am interested in what the final resolution will be.

Jeff
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
jcuzzola,

Is there any chance we could have an example of the original accuracy problem you mentioned?

Author

Commented:
Good Morning Fellas,

I hope you had a great weekend...:)

Just got in and I am determined to solve this problem. I've got too much going on.  I will apply your thought  and get back asap...
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
Did you ever get round to changing the data type of your 'Viable' field?  And what about that WHERE clause too - it still looks like you're updating too many records to me!

Author

Commented:
I must not have submitted the last comment I posted. I managed to store the value using the Use the ControlSource Property to Store Calculated Values as described on the MS website, http://support.microsoft.com/kb/209172 It corrects and restores the value if any of the variables change as well. I thought by storing the value, the problem I'm having retrieving the last record would be corrected. It is not so. I'm gonna try and create a basic query containing the basic variables and get back to you in a new question. I have to solve this problem... It is for a critical report.

Thanks for stying with me...

JCz
Jez WaltersData Analyst
BRONZE EXPERT

Commented:
It'd be a good idea to post a link to the new question here, when you're ready.  A bit more detail of the source of the problem would be useful too.

Best of luck!  :-)

Author

Commented:
Thanks

Will do thanks.

Author

Commented:
The link to the new question is here...ID: 23119092  

Explore More ContentExplore courses, solutions, and other research materials related to this topic.