Query Field Using Other Fields in the Query

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

I have a query where I have two calculated fields using data from two
different tables. I want the query to also contain a field showing the sum
of these two calculated fields, but I cannot seem to make that work. From
Query design, I selected Builder, then the current query, then in the
expression box I typed:
[first calculated field]+[second calculated field]

When I ran the query, instead of calculating the expression, a parameter
input box opened asking for the first calculated field. Where am I going
wrong.
 
If you are using Jet, then you probably either have a typo for the
field/expression you are prompted, either you use a alias of an expression
in the where clause (or in the order by clause).



Vanderghast, Access MVP
 
Forgot to say what to do in the second case: replace the alias with the
expression it stands for:

NOT:


SELECT a+b AS c
FROM somewhere
ORDER BY c


BUT


SELECT a+b AS c
FROM somewhere
ORDER BY a+b



as example.



Vanderghast, Access MVP


Michel Walsh said:
If you are using Jet, then you probably either have a typo for the
field/expression you are prompted, either you use a alias of an expression
in the where clause (or in the order by clause).



Vanderghast, Access MVP



Alan said:
I have a query where I have two calculated fields using data from two
different tables. I want the query to also contain a field showing the
sum
of these two calculated fields, but I cannot seem to make that work.
From
Query design, I selected Builder, then the current query, then in the
expression box I typed:
[first calculated field]+[second calculated field]

When I ran the query, instead of calculating the expression, a parameter
input box opened asking for the first calculated field. Where am I going
wrong.
Click to expand...
Click to expand...
 
Most of the time you can not use a calculated field in the same query where
it is calculated. The problem is that it is called before it is created.
Use the complete calculation again.
 
Back
Top