subtract two columns within a query

[penfold1992]

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

 

[pr2-eugin]

Could you please show the Query?

 

[plog]

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]

@ 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]

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:

Function DoComplexCalculation(n1, n2, n3, n4)

Variable1 = n1 - n2
Variable2 = n3 - n4

Variable3 = Variable1 - Variable2

DoComplexCalculation = Variable3

End Function

 

[penfold1992]

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]

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]

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?