Solved How To Handle Calculated Fields Dependant On Parent Calculated Fields (2 Viewers)

dalski

Member
Local time
Today, 10:26
Joined
Jan 5, 2025
Messages
213
On math heavy queries what is the best way to handle titled? Was advised that other db systems would fail on a calculated field which obtains it's value from a calculated field defined in that query. So I'm not doing that. In an ideal world this makes it much easier but I will want to use MSSQL later.
  • I have 30 calculated fields to determine relevant in this query (that's results; without fields in the query itself), & 15 variables.
UDF's are great in substitution to a calculated field. BUT as they get more complicated each next calculation requires the result of the parent... & each function is running again to get it's defined value so I'm concerned on performance here.
To counter this I think a single class object (just a single predeclared class object which encapsulates all the math) may be better; retaining the parent's value in a property/ module-level variable (properties probably aren't even needed as everything actioned is within the class). The downside being sometimes I will run calculations which are not required; affecting performance.

What is my best route of action here?

On a sidenote - will passing the result returned from a function in a field of the query to another function bite me in the ass when converting to MSSQL?
 
Last edited:
This is a thought, not a guarantee of better performance or better results, but... consider a layered query where you compute all of the "first-level" values in a query but then compute any "second-level" values from computed fields in the first-level query. You can layer queries up to a limit.

as they get more complicated each next calculation requires the result of the parent...

How deep do you have to get? A couple of generations is probably OK, but the deeper you get, the worse it gets. Can you give us an example of what you are doing?

As to having user-defined functions in a field and later converting to MSSQL, I must defer that answer to my colleagues as I was never involved in developing MSSQL interfaces. We had ORACLE at the site where I used Access with active SQL back-ends.
 
Think we need more specifics. I have two hunches based on nothing you've said, just experience here:

1. Your math could be more efficient. 30 fields based on 15 inputs is a lot, and then you say that some calculations feed into other calculations downstream. Perhaps some of those intermediary steps aren't necessary, maybe you could consolidate some, or maybe there's a way to reach the end goal with less steps.

2. Maybe Access isn't the right tool. Maybe you store your data in Access, run a query there and paste the resulting data into an Excel template that does all the heavy lifting. Or maybe Access is the right tool, but your approach is wrong. Instead of nested SELECT queries you have a series of Action queries that produce a non-normalized dataset upon which you do whatever you need to do with your data.

To determine the right path, we'd really need more information. Big picture--what is the end result of all these calculations? What are you doing with that data ultimately? Brass tacks--give us an example and walk us through one of the most complex calculations.
 
I agree, we don't have enough info on the calculations. How deep is certainly relevant but how many fields, not so much. I agree with Doc. I always use separate layered queries rather than UDF or classes for this type of problem. The layered queries make testing significantly easier than doing the whole thing in a single large query with multiple layers of subqueries because you can run qry1 and look at the results from that step. Then run qry2 and look at those results. Then run qry3, etc. I've probably not gone more than 5 levels so I don't know what the limit is. When you send the "final" query to Access, it optimizes the nesting for you so you don't need to worry too much about that. In fact, there is at least one bug that nesting two queries solves and it has to do with filtering based on the right table of a left join. If you do the filtering in a single query, you don't get the correct results but if you create a separate query that adds the criteria for the right table and than create a query that left joins the left table to the query of the filtered right table, the results are correct. This seems to be a Jet/ACE issue and is not a problem for SQL Server.
 
consider a layered query where you compute all of the "first-level" values in a query but then compute any "second-level" values from computed fields in the first-level query. You can layer queries up to a limit.

How deep do you have to get? A couple of generations is probably OK, but the deeper you get, the worse it gets. Can you give us an example of what you are doing?
Thanks Doc, yes that's the route I think. Any idea on the limit for layered queries? I said 30 to be safe but I recon I may be able to get it to around 8 separate queries. They make the calculations harder without them. I'm not sure exactly yet, it's a little difficult defining the dependancies exactly. Have it designed in Excel & trying to decipher atm.

1. Your math could be more efficient. 30 fields based on 15 inputs is a lot, and then you say that some calculations feed into other calculations downstream. Perhaps some of those intermediary steps aren't necessary, maybe you could consolidate some, or maybe there's a way to reach the end goal with less steps.
Yeah I may be able to break-down to around 8 different queries.
Big picture--what is the end result of all these calculations? What are you doing with that data ultimately? Brass tacks--give us an example and walk us through one of the most complex calculations.
It is an Estimating application, arriving at rates but with far better features than the vast majority on the market so I can't go giving away one of the main features. Queries are the best route as the data is constantly changing & needs to refresh. Excel is definitely not the route; I have calculations a system built in Excel; it is not the tool for this. Yep appreciate fully info is insufficient, I'm just after a general gist guide; which I've gotten from yourself, Doc & Pat here. I do appreciate your suggestion of an non-normalized data-set. As there are a lot of records/ manipulation we need the speed of SQL & the ACE db engine as much as possible I think.

I always use separate layered queries rather than UDF or classes for this type of problem. The layered queries make testing significantly easier than doing the whole thing in a single large query with multiple layers of subqueries
Thanks Pat, really helpful. This sounds like the definite route to go. Just to clarify there are around 10 conditional statements in these calculated fields (5 fields containing around 2 separate nested conditional statements each) so these will slow the query down I believe (from previous learning from you so thanks for that).

Calculated Fields will be much easier from my design/ construction point of view & was the answer I was hoping for. The only thing I'm worried about is the conditional statements.

Thank you, I got what i wanted with the limited info supplied; appreciate all your endeavours.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom