UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Updating Table Field From A Sum Of A Field In Another Table, Access 2016    
 
   
ollyhutsy
post Oct 17 2019, 02:33 AM
Post#1



Posts: 40
Joined: 16-October 19



I have two main tables at the moment which are: Jobs and Job Detail. In the Job detail i will have sub Jobs which are linked on the order number with hours clocked against each job. These hours can be inputted on various different forms. I need there to be a constantly updating field for the Sum of the hours spent on a job, in relation to that order number. At the moment i have got a sum of hours in a subform (SumOfHours), Which is just a simple expression. Then used the code:

Private Sub Form_Current()
Me.Actual_Machine_Time = [Job Detail].Form![SumOfHours]
End Sub

To input this into my control source. This works fine however I have other forms which need the calculation of the sum, and this data will not be inputted unless the record has been opened in this form.

Any Suggestions?
Go to the top of the page
 
arnelgp
post Oct 17 2019, 02:47 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


suggest you use Query in Job details.
on the query add a Calculated column that will sum the hours.
add the calculated column to your subform.
therefore you don't need to add code to the Current event of the form.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Oct 17 2019, 07:22 AM
Post#3


UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA


In addition, I'd like to be sure we're not talking about STORING the calculated Sum in a table, but only DISPLAYING it in relevant forms....

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ollyhutsy
post Oct 18 2019, 05:45 AM
Post#4



Posts: 40
Joined: 16-October 19



I have done using a query sum, However if i did want to store the value into my table afterwards how would i do this?
Go to the top of the page
 
arnelgp
post Oct 18 2019, 06:13 AM
Post#5



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


it is not advisable to store the value to your table.
you can get it from the query.
this saves you from updating your table if the subform's
record increases/decreases.
the value from the Calculated field on the query is
always updated.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Jeff B.
post Oct 18 2019, 07:43 AM
Post#6


UtterAccess VIP
Posts: 10,326
Joined: 30-April 10
From: Pacific NorthWet


As <arnelgp> and George point out, you might be better off NOT storing the calculated value. If you decide you MUST, then you MUST also figure out how to update/correct that stored value when someone like me, with BFF (big fat fingers), makes an initial mistake in entering hours and goes back to correct it...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
June7
post Oct 18 2019, 02:40 PM
Post#7



Posts: 968
Joined: 25-January 16



Normally, aggregate calcs are not saved to table. Enter transactions and do aggregate calcs when needed. Review http://allenbrowne.com/AppInventory.html

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2019 - 04:25 AM