you can use separate queries to build it up e.g.
qry1
SELECT ID, fld1*2 as Num
FROM myTable
qry2
SELECT myTable.*, Num*2 as Num2
FROM qry1 INNER JOIN myTable ON qry1.ID=myTable.ID
WHERE Num*4>20
Thank you for expressing it better than I apparently was able to.Criteria work on the original source not alias. Your Alias is a calculation
You probably should use a function as mentioned; even using any one of multiple query fields. Assuming the result is a long integer, you could write it as
in query grid: Expr1: ReturnVal([field1])Code:Function ReturnVal (qryFld) As Long Select Case qryFld Case "field1" do this math Case "field2" do other math Case .... End Select End Function
Not sure the sql approach is all that viable. I got the impression that there are many fields in this query that will be calculated, not just one.Wouldn't the sql approach require at least a linked query for every calculation and criteria based on it?and I wanted to refer to it many times in other columns
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
maybe, maybe not, not enough info provided by OP to make a judgement. Either way you would only store the calc in one place. The issue is with wanting to apply criteria to the calculated value(s). As the OP acknowledges databases work in a completely different way to excel, so trying to map an excel calculation process to a database is almost certainly going to failWouldn't the sql approach require at least a linked query for every calculation and criteria based on it?