weird behaviour of calculated fields (1 Viewer)

senseidan

Registered User.
Local time
Today, 18:49
Joined
Jan 25, 2018
Messages
38
I have created a report that displays with some delay the results of it's calculated fields. It's about 15-20 seconds delay that I assumed was due to too much computing effort. I thought it was normal because, during this time, simple arithmetic operations were performed with about 20,000 records. So I focused on finding ways to speed up this process but, to my total stupefaction, I accidentally spotted that if I click on the calculated fields, the results are there, without any delay. Please help me understand what's going on and how I can "convince" those fields to show the results when they are available, without this weird delay.
 

senseidan

Registered User.
Local time
Today, 18:49
Joined
Jan 25, 2018
Messages
38
UPDATE: I noticed that the delay only appears on Report View (not in Print Preview) and it seems it is related to the display of the results rather than their calculation because when I try to scroll, even just a little, the results disappear and when I stop scrolling they reappear after the usual delay.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:49
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you performing the calculations in the report or in the query? Just curious...
 

Mark_

Longboard on the internet
Local time
Today, 11:49
Joined
Sep 12, 2017
Messages
2,111
I have noticed similar issues at times when ACCESS is doing the same process repeatedly for... reasons... that is isn't sharing with me. You may find doing your calculation ONCE prior the report opening and assigning it to a variable, then just referencing the variable will cause this delay to go away.

Alternately, you can work through everything that is calling your code to find where it is being called multiple times, if this is actually the cause.
 

senseidan

Registered User.
Local time
Today, 18:49
Joined
Jan 25, 2018
Messages
38
@theDBGuy
Hi,
Most of the calculations are made by queries and a small part is made in the report.
To be more precise, what I'm trying to do is a kind of cash flow. My report should show in detail the current day transactions and finally show how much money I have, based on all previous transactions.
Let's say that on the first day I get $ 100 out of 10 transactions and spend $ 30 out of 5 transactions. At the end of the day I have $ 70.
The next day, I collect $200 out of 5 transactions and spend $40 out of 2 transactions. At the end of the day I have $160 plus $70 of the previous day = $ 230.
My strategy was to collect the necessary data from the tables that contained them and to create a union query. Then, another query makes the calculations up to the current day to find out how much money the day started.
Finally, in the report, I detail the transactions of the day, make the calculations for that day only, and I add the result of the previous days ( DLookup in the query which made all calculation except current day)
So, can I have yours "2cents"?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:49
Joined
Feb 19, 2013
Messages
16,553
a lot depends on whether your tables are structured correctly and properly indexed. From your description you are using quite a complex method for something which should be fairly straightforward. This may be due to an inherent structural issue or perhaps method of approach.

Assuming your transactions table uses these columns

tblTrans
TranPk...TranDate...TranDesc….TranAmount
1...……….1/1/2019...deposit......200
2...……...2/1/2019...expense1...-30
3...……...2/1/2019...expense2...-20
4...……...3/1/2019...expense3...-100
etc

TranID and TranDate should be indexed

This sql will give you opening balance and each transaction for the specified day

Code:
PARAMETERS [Enter Date] DateTime;
SELECT IIf([tranDate]<[Enter Date],[Enter Date],[TranDate]) AS TransDate, IIf([tranDate]<[Enter Date],"Opening Balance",[TranDesc]) AS TransDesc, Sum(tblTrans.TranAmount) AS TransAmount
FROM tblTrans
WHERE (((tblTrans.TranDate)<=[Enter Date]))
GROUP BY IIf([tranDate]<>[Enter Date],[Enter Date],[TranDate]), IIf([tranDate]<[Enter Date],"Opening Balance",[TranDesc]), IIf([tranDate]<[Enter Date],0,1)
ORDER BY IIf([tranDate]<[Enter Date],0,1)
For the closing balance, you can have a control on your form or report to sum the TransAmount field
 

senseidan

Registered User.
Local time
Today, 18:49
Joined
Jan 25, 2018
Messages
38
@Mark
Thank you very much! Your suggestion, to do (all) calculation prior the report opening, WORKED!
I have deleted all the calculations in the text boxes and used temporary variables to bring the results into the report. Now all results appear instantly, as they should.
It was strange to note that even in text boxes where there were no calculations (DLookup only) there were delays and now, using an indirect method (assigning a DLookup value to a variable then instructing the report to use the value of that variable in the text box), I no longer perceive any kind of delay.
You were right, MS Access is doing something behind the curtain and not telling us what.
 

Mark_

Longboard on the internet
Local time
Today, 11:49
Joined
Sep 12, 2017
Messages
2,111
Very very glad it all worked out!

For data normalization, do you have one table that you enter journal entries into or is this a "One table for IN, one table for OUT" arrangement?
 

senseidan

Registered User.
Local time
Today, 18:49
Joined
Jan 25, 2018
Messages
38
Hi Mark,
Me too I am happy and a little amazed by the result.
I would have liked to understand what was going on, but now I'm more concerned about the problem that you and CJ London have raised (sort of).
I'm thinking if I can rely on the method of calculating my balance each time I view the report. Now there are about 20,000 transactions but this number will increase over time. In addition, and here I approach the answer to your question, I have to compile the data from three different tables, before the calculations, each containing inputs and outputs. It's a situation that does not thank me but I can not avoid it.
 

senseidan

Registered User.
Local time
Today, 18:49
Joined
Jan 25, 2018
Messages
38
@CJ London
I want you to know that I still analyze your answer even if it does not exactly match my situation. You're right, I've inherited a situation that has made me make things more complex. But I do not claim to have found the best solution. I want to test the code you sent to me and figure out if I can find a solution for the situation when the calculations will become more numerous as the transactions are added. Until then, thank you for the magnitude of your response.
 

Mark_

Longboard on the internet
Local time
Today, 11:49
Joined
Sep 12, 2017
Messages
2,111
To find out where you are getting this massive slow down, one thing you can do is put a little checking code in your current code. I'd have labels on the report that are set to "0" to begin with. Each time a piece of code is hit it updates its matching label by adding 1 to it. This SHOULD let you quickly find out of one or more pieces of code are getting hit repeatedly. Call it a 15 minute "Sanity check" if you will.
 

Users who are viewing this thread

Top Bottom