subtract two columns within a query (1 Viewer)

penfold1992

Registered User.
Local time
Today, 00:09
Joined
Nov 22, 2012
Messages
169
hello again :)

I have two calculated columns in my query
for example...
table.num1 - table.num2 as "Col1"
and
table.num1 - table.num3 as "Col2"

I then want another column equal to:
Col1 - Col2
I know that I could say:
(table.num1 - table.num2) - (table.num1 - table.num3)
but I am wondering if there is an easy way to just take two calculated columns of the query to use in the calculation of another column.

doing something like:
Col3: Col1 - Col2
doesnt work because it doesnt see Col1 and Col2 within the tables.

Is this possible?

Kind Regards,
Penfold
 

plog

Banishment Pending
Local time
Yesterday, 19:09
Joined
May 11, 2011
Messages
11,613
I then want another column equal to:
Col1 - Col2
I know that I could say:
(table.num1 - table.num2) - (table.num1 - table.num3)
but I am wondering if there is an easy way to just take two calculated columns of the query to use in the calculation of another column.

First as a pedantic amatuer mathmatician, I must point out that you can reduce your equation to:

table.num3 - table.num2

Second, to accomplish what you want, you could use a sub-query based on the query you are working on now. The query you have now will have Col1 & Col2 in them and any query built upon it could reference them and use them in an equation (provided they return the correct datatypes for the operators you use).
 

penfold1992

Registered User.
Local time
Today, 00:09
Joined
Nov 22, 2012
Messages
169
@ pedantic amatuer methmatician. I knew someone would do this with my example!
unfortunately the forumla isnt as simple as that. The formula takes a long time to compute with the large data size I am using. its much easier for a computer to compute a subtraction than it is to use my formula twice and subtract the second number away from it.
It looks like I will either have to create a sub query or plug the entire equation in.
 

plog

Banishment Pending
Local time
Yesterday, 19:09
Joined
May 11, 2011
Messages
11,613
Another thing you could do is build the entire calculation into a custom function in a Module. Your query would looke like this:

ComplexCalculation: DoComplexCalculation(num1, num2, num3, num4)

Then in the module you can use as many lines and variables as you want:


Code:
Function DoComplexCalculation(n1, n2, n3, n4)

Variable1 = n1 - n2
Variable2 = n3 - n4

Variable3 = Variable1 - Variable2

DoComplexCalculation = Variable3

End Function
 

penfold1992

Registered User.
Local time
Today, 00:09
Joined
Nov 22, 2012
Messages
169
Another thing you could do is build the entire calculation into a custom function in a Module.

yes, this is currently what I have... but that still doesnt reduce the run time unfortunately, that was the real problem :(
 

plog

Banishment Pending
Local time
Yesterday, 19:09
Joined
May 11, 2011
Messages
11,613
Last idea, then I will stop. The key is reducing calculations. Even if you could get your initial idea to work, it would still require the same amount of calculations--its not like col1 one would be stored and the value reused, it would do that calculation as often as it had to.

So, if you have a calculated value that is used often in the final calculation you might think about creating a table off a query that does that calculation, then using that table in further queries.

For example if CalculatedValue was a based on 7 other fields, and itself was used in four other calculations down the line, you might create a query that determines CalculatedValue, send it to a table then build the other queries off of that table. That way it just uses the value and doesn't have to recalculate it for every instance its used.
 

penfold1992

Registered User.
Local time
Today, 00:09
Joined
Nov 22, 2012
Messages
169
Another thing you could do is build the entire calculation into a custom function in a Module.

Ok there is a problem with this method...
I want to link this query to an excel spreadsheet but the query doesnt appear on the list of queries you can choose from when creating a table/query link to excel...

So i tried to create an SQL type instead but I receive the message:
"Undefined function 'Service_Time' in expression."

Service_Time being the name of the function I created.
Is there a work around?
 

Users who are viewing this thread

Top Bottom