Re: Calculated field in a calculation.

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 05/01/04 

Date: Sat, 1 May 2004 07:27:51 +0530

hi lind,

In RDBMS theory the computed expressions that are used in the SELECT
statements like

select (col1+col2) as 'exp1', (col3+col4) as 'exp2'

gets executed all at the same time and not one after another. Hence one
expression is not visible to other expression. Access handles these kind of
"query expressions" in different way, you really can not compare SQL written
in Access and SQL Server.

You can only reference these kind of expressions in Order by clause.

Ex:
use northwind
go
select customerid + 'x' as 'cusid'
from customers
order by cusid

--but following will be errored out.
use northwind
go
select customerid + 'x' as 'cusid', cusid + 'y' as 'cusid2'
from customers

The work around would be to use derived tables.

Ex:
use northwind
go
select cusid,cusid + 'y' as 'cusid2'
from
(select customerid + 'x' as 'cusid'
from customers) DerivedTable --this is evaluated as derived table 

-- 
Vishal Parkar
vgparkar@yahoo.co.in