Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-14-2018, 03:41 AM   #16
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,439
Thanks: 92
Thanked 1,831 Times in 1,701 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Does calculated fields cause database going slow?

Quote:
Originally Posted by Leo_Polla_Psemata View Post
I have been convinced that calculated fields on tables is a heavy database sin.
I would agree that calculated fields are almost always the wrong solution.
So don't do this

Just calculate A based on B,C,D as and when required in a SELECT query.
Do NOT store the values in a table.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 12-14-2018, 06:15 AM   #17
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,591
Thanks: 62
Thanked 1,208 Times in 1,108 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Does calculated fields cause database going slow?

You can, if you feel it is necessary, build an UPDATE query that would store the results of the computation. A class module might represent overkill, too much effort when an easier method is available.

Understand that theoretically, you can put anything in the table that you want. It IS, after all, YOUR database. BUT what you just described is a technical design violation. Let me explain.

When you have normalized data tables, then one (out of many) properties that should apply is that any field in a well-structured table depends ONLY on the primary key of the table. For your table where field [A] depends on [B], [C], and [D], the ONLY way that would be within "proper" design guidelines is if [B], [C], and [D] were part of a compound key that was the prime key of that table. Otherwise, [A] depends on something that is NOT the PK of the table, and this usually leads to difficulties.

Know this: NOTHING in Access absolutely requires the presence of a table per se, though MANY things demand a recordsource. A table is a valid recordsource. But so is a query based on a table, even if it contains computed values. So if you have this field [A] that needs to be present in a report, compute it on the fly either in a QUERY that underlies the report, or in the appropriate control on the report itself.

As to storing a computed value, here is the question that will forever bug you every time you need to consider a "computed field" solution: Can this field [A] ever be updated to override the computation? AND if so, how would you know that an override occurred?

This comes down to two scenarios: (a) you can never override the computation. In which case you can hard-code that in the report or form and not even bother to store it. OR (b) you CAN override the computation, in which case the fact that the field is computed might get in the way, because its definition no longer would match its value.

Let's toss one more stink-bomb. So you have these fields [B], [C], and [D] that are mish-mashed together some way to yield field [A]. What is supposed to happen if any one of those fields changes in value? Obviously, if they are part of a compound primary key, that is unlikely, since one of the questions regarding selection as part of a PK is mutability. But if they are NOT part of the PK, then if they can EVER change, what happens to [A]? If you have the computation in a query then opening the query automatically updates the computations.

I'm explaining this to you in this way so that perhaps you can see the viewpoint that is commonly taken in terms of data design. These are the kinds of things you must think about for each data element in your DB. Sometimes a field is just an input field and the question is trivial. Sometimes, though, it takes a bit more forethought.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 12-14-2018, 09:30 AM   #18
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,384 Times in 3,279 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Does calculated fields cause database going slow?

Quote:
This comes down to two scenarios
@ Doc, I would make that three. The third scenario is where the calculated value must never change. for example an invoice line total. You might store the invoice quantity and price and calculate the value - but if you change the calculation down the line then that value would change which means when you print it out for the benefit of the taxman 2 years on they might see a different value to the one that went to the customer. For invoices I always recommend storing all elements of the calculation plus the results |(e.g. price, quantity, discount percentage, discount amount, NetValue, tax percentage, tax amount, TotalValue). I would also store the totals in the invoice header for the same reason - just in case a line record goes missing

It's a bit like your a) but you can't hard code the value - and with the possible exception of quantity, all the other values will have come from other tables rather than the same table


__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
calculated fields cant see in the database Dhanu Forms 36 12-02-2017 09:18 AM
Calculated text box not working with slow connection to backend tomusn83 Forms 4 12-21-2015 05:17 AM
Calculated boxes slow to draw?? NZArchie Reports 4 10-12-2011 12:46 PM
populate textbox with calculated value taking values from database and form fields. pinky Modules & VBA 7 07-10-2009 06:20 PM
Slow Database? HELP! phn General 1 04-04-2006 05:21 AM




All times are GMT -8. The time now is 11:54 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World