use an Update query? (1 Viewer)

QMDirk

Member
Local time
Today, 03:13
Joined
Nov 16, 2019
Messages
52
Hey everyone. I think this is the right forum for this question. I have a form that tracks Production Downtime. The table contains the fields: [Total Minutes], [Time Down], and [Time Restart]. A form uses the function: =Sum(DateDiff("n",[Time Restart],[Time Down])*-1) to show the difference between [Time Restart] minus [Time Down] in field [Total Minutes]. The problem is, of course, that the table doesn't update field [Total Minutes]; the values are only displayed on the form. So the question is, is there any way to update the table with the calculated values from the form? (and if anyone says "use a calculated field in a query, I'm going to stab myself in the jugular with a butter knife! I don't know what the f*&k that means!). Thanks. :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:13
Joined
Oct 29, 2018
Messages
21,358
Hi. People say that because it's not recommended to store calculated values in a table. However, I'm a little confused, why is it called Total Minutes, if there's one of them for every record? When you use Sum(), that means you are calculating one result for all the records. So, which record were you trying to update the Total Minutes?
 

plog

Banishment Pending
Local time
Today, 05:13
Joined
May 11, 2011
Messages
11,613
What's the opposite of risk? Opportunity? At the opportunity of having you puncture your jugular, let me be the first to say you shouldn't use calculated fields in tables.

Now let me turn it back on you. Why must this value be in the table? What's it do for you in there? You've already explained that your form doesn't even need the value--instead your form does the math on the underlying fields and then displays that. Where exactly are you using [Total Minutes]?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Feb 19, 2002
Messages
42,981
I am not disagreeing with plog. 99.999% of the time, you really do not need to store calculated values but occasionally it makes sense. For example, your process might rely on querying the difference field and so you might want an index. Given the new options with Data Macros, you could make a calculated field and actually create an index on it. Access would manage the updating if either the start or end value changes.

Baring that, take the expert advice and calculate the difference in a query that is the RecordSource for your form or report.
 

plog

Banishment Pending
Local time
Today, 05:13
Joined
May 11, 2011
Messages
11,613
Have you met people? If you tell them that there's a .001% chance of something applying to them, they don't see a minuscule number, they see a number that's not 0 and know the situation they have is absolutely that exception. Then, especially on this forum, someone comes along and gives them the hack that allows them to do it their inefficient way.
 

Isaac

Lifelong Learner
Local time
Today, 03:13
Joined
Mar 14, 2017
Messages
8,738
(and if anyone says "use a calculated field in a query, I'm going to stab myself in the jugular with a butter knife! I don't know what the f*&k that means!). Thanks
Okay, having read this thread over a few times, what I think you meant by that doesn't have anything to do with people saying "it's a bad idea to store calculated values in tables", I think you may have meant something more like, "Don't just tell me to use a calculated field in a query, be more specific, as I don't know what that means in my case".
Really I have no idea - just my guess as to your intention there. Feel free to tell me it was something totally different.

Either way, the two potential meanings are, as others have mentioned, related. In Access databases, with relatively "small data", it's usually better to perform calculated values at query run time - forming whatever expression at that point that you need to, rather than materializing that in a table. But, sometimes even in Access, and many times in other environments, it's not a bad idea.

Brief summary of the 2 sides here

But to answer your question. How to take a control value from a form, and get it into a table, where the control isn't bound to a RecordSource field, but rather contains an expression/calculation? You would include the field TotalMinutes in the form's RecordSource, and drag it onto the Form as a bound control. Then, you might add code in the form's BeforeUpdate event, and assign the value: Me.ControlNameOfTotalMinutes.Value=ControlNameOfCalculation.Value
 

isladogs

MVP / VIP
Local time
Today, 10:13
Joined
Jan 14, 2017
Messages
18,186
Agree with previous comments that calculations should almost always be done in a query.
The only times I ever store calculated values in a table is where the data is based on a very large number of records e.g. >1 million, the calculated values are needed on a regular basis and where doing the calculation in a query was so slow as to be unacceptable. An example of such an exception in my apps would be average % attendance by student year group and gender. In those very rare cases, I run code to update the calculated values and store them in a table
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:13
Joined
Feb 19, 2002
Messages
42,981
Have you met people?
I know a couple:) And you are absolutely correct. To most, "not 0" means yes. However, using data macros is a relatively safe way of doing this since the calculation is performed by the database engine so regardless of where/how the change happens, the calculation will be properly updated. The danger in storing calculated values is that there is always the possibility that the original developer or more likely some poor schmuck who comes along later who doesn't know the app intimately, makes a change and misses the fact that the change affects a stored calculated value. But, the data macros avoid that danger.
 

Users who are viewing this thread

Top Bottom