Calculated Fields in Query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Calculated Fields in Query

Bryce Agnew

4 years ago

Hello Richard, I have been working with my MS Access application for about 9 months now.  I am previous C/C++ programmer.  When I started a new project I used your videos for assistance.

I am going to provide a simplified example of what I have found.  For this example we have a query which has a few fields that are sourced from a static database, and the rest of the fields are calculated fields.

So for my example it looks like this:
Date |  Static Long Number Num1 | Static Long Number Num2 | Static Long Number Num3 | Calculated Field A | Calculated Field B | Calculated Field C | Calculated Field D

The Date is simply the date from the records returned from the query, which filters records from the source database based on all the dates that are between a user selected start and end date.

A is simply Num1 x Num2
B is A X Num3
C is B squared
D is C x 100

What I initially thought when I did this query in MS Access is that MS Access would calculate and remember the results, but it does not seem to do that.
So if Num1 = 10, Num2 = 1000, and Num3 = 10,000 for a record in my selected date the query results would be:

       Num1   Num2    Num3       A          B          C      D
Date | 10   | 1000  | 10,000 | 10,000 | 100,000,000 | 1e16 | 1e18 |

However what I believe is really happening is when A calculates it does the simple multiplication, when B calculates it does not grab the 10,000 result from A, it recalculates the Num1 x Num2 again, then does the multiplication with the 10,000 in Num3.  When C calculates it recalculates B, which causes a recalculation of A, then recalculates B again  (since it needs to square it) which recalculates A yet again, to come up with the answer.  D Recalculates C, which recalculates B, which recalculates the A/B above all over again.

So for this very simple example of just one record being returned by my query Field A was calculated 6 times, B was calculated 5 times, C was calculated 2 times, and D was calculated 1 time.  Access does not remember that it already calculated A and to simply hold 10,000 there.

My program has much more complicated calculations, many written in VBA.  This really bogs down as MS Access repeatedly calculates values previously already calculated for all the calculated fields.

So hopefully this all makes sense.  I cannot find anyone talking about this in any video or providing any sort of solution to the repeated MS Access calculations.  It would be great if I could tell MS Access to just calculate once and hold the value in the query field.

So my two questions are:
1. Is what I have explained your (or the general) understanding of how MS Access executes calculated fields in a query, or has my investigation not been correct?
2. Is there any way to have MS Access only calculate a field once and just reference that answer and not re-calculate numerous times?  Clearly if my query has hundreds or thousands of rows being returned all of this "repeated" calculations really take time.

Any insight Richard or anyone can provide would be fantastic.

     Thank you,
     Bryce

	Dan Jackson @Reply 4 years ago
I'm very inexperienced but maybe a simple solution is the right one? From what i understand, Queries and Calculated fields in Forms/Reports are always generated on the fly so yes, it will always want to recalculate at every step. Would a separate table to store the calculations and an append query help? Maybe the more experienced will have a better solution but thought i'd throw that in there

	Bryce Agnew OP @Reply 4 years ago
Thank you Dan. I have considered that, and might go that path if I need to. The problem is the actual program/query is much more complicated then my simple example, but this is a possibility. If there is no solution and all calculated fields are generated on the fly every time they are referenced I may need to go with that approach.

Richard Rost @Reply

4 years ago

Bryce, I'm not sure I understand your question. Basically when you run a query, Access calculates any "per record" fields as needed. So if you have A and B as fields in your table, and in your query you have:

C: A+B

Then each time you run that query, C will be recalculated. If you want that value to be saved in the table, then you'll have use a form event somewhere, but that's usually unnecessary.

I don't understand the rest of your question though. Too much data. Can you simplify it for me? Explain it as if you were talking to a 3rd grader... and keep it as short as possible. I think I'm losing the details because there's just so much to read there.

Bryce Agnew OP @Reply

4 years ago

To simplify using your example, if field C: A+B and I have a field D: C+100 and a field E: D * 2. Then when C calculates it does the A+B which is expected, but when D calculates and references C, Access does not realize that it already calculated C so it does A+B again, then adds the 100 to it, then when it calculates E it goes back to D, which goes back again (now for the third time) to C and adds A and B again, then adds 100 again for D (for the second time), then multiplies by 2 for E. It is the repeated calculations for fields that have already calculated that I am questioning, I am fine that each time the query runs they calculate once per row returned, it is just having them calculated multiple times per row returned seems inefficient.

Thanks,
Bryce

	Adam Schwanz @Reply 4 years ago
No matter what you do if C is used in a calculation, it has to go back and find out what C is. If these are numbers that dont change and your concerned about the speed, you probably will have to make table entries for them instead. Ive had to do this with one of my financial forms because it was too complex for a query. Then just use an update query to set the values.

	Adam Schwanz @Reply 4 years ago
However if they are numbers that constantly change, i dont think you're gaining much.

	Richard Rost @Reply 4 years ago
I haven't run in to this situation with simple fields like this, HOWEVER, if that's the case, you may have to calculate C in Query1, and then feed those results into Query2 where you calculate D, and so on.

Adam Schwanz @Reply

4 years ago

I don't think he's having trouble with the numbers Richard. Maybe I'm mistaken though, I think he's saying when he does A+B=C he can't go on and say C+D=E, instead access has to go back and figure out what C is. So it ends up being like A+B+D=E, hence extra calculations.

Say A = 1 and B = 2
1 + 2 = C
C = 3

It doesn't just know that C is 3 at that point, so 3+D=E, instead it goes back and does another calculation to find what C is, doing A+B over again.
C+D=E
A+B+D=E
1+2+D=E

The only way I think to get around that is to store values in the table.

	Adam Schwanz @Reply 4 years ago
If I'm wrong and you're getting wrong numbers though, yea try Richard's suggestion. I wouldn't expect that to be the case in simple calculations though.

	Richard Rost @Reply 4 years ago
Well, I'm sure one of the two of us is correct. LOL

	Adam Schwanz @Reply 4 years ago
LOL

Bryce Agnew OP @Reply

4 years ago

Adam you are correct. The calculations/numbers are correct, it is the process of "Re-Calculating" that concerns me. In my actual case some of my more complicated calculations are done in VBA code, and when I reference that the code re-executes even though it had previously provided an answer. So it seems the answer is to store my answers in a table, and have the other calculations that run reference the table. Since my calculated fields are a combination of Access expressions sometimes, and VBA sometimes they would all change. I was hoping there was some query parameter that changed the behavior. Thank you all for your input!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View