[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1188
  • Last Modified:

Storing data from a calculated field into a table.

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
0
jcuzzola
Asked:
jcuzzola
  • 12
  • 7
  • 3
  • +2
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
first, it is not advisable to store calculated values in the table. ( you can always get the values using queries)

0
 
Rey Obrero (Capricorn1)Commented:
select  ([TxtViable]/[TxtTotal]) * 100 as PercentViable
from  tbl_U2_Spinners
0
 
jcuzzolaAuthor 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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
clarkscottCommented:
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
0
 
JezWaltersCommented:
Won't 'back-calculating' the existing data present you with the same inaccuracy problem?
0
 
JezWaltersCommented:
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

0
 
JezWaltersCommented:
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

0
 
jcuzzolaAuthor Commented:
Just got back from lunch I will try it but I may need it to loop. I will fool witj it now....
0
 
jcuzzolaAuthor 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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
JezWaltersCommented:
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!
0
 
JezWaltersCommented:
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.
0
 
JezWaltersCommented:
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!
0
 
JezWaltersCommented:
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.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
JezWaltersCommented:
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.
0
 
JezWaltersCommented:
My most humble apologies Jeff - you can't see the wood for the trees sometimes!  :-)
0
 
Jeffrey CoachmanMIS LiasonCommented:
No problem.
;)
Good luck with this Q, I am interested in what the final resolution will be.

Jeff
0
 
JezWaltersCommented:
jcuzzola,

Is there any chance we could have an example of the original accuracy problem you mentioned?
0
 
jcuzzolaAuthor 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...
0
 
JezWaltersCommented:
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!
0
 
jcuzzolaAuthor 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
0
 
JezWaltersCommented:
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!  :-)
0
 
jcuzzolaAuthor Commented:
Thanks

Will do thanks.
0
 
jcuzzolaAuthor Commented:
The link to the new question is here...ID: 23119092  
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 12
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now