Calculated field

[Asha]

I need to make a calculated field based on a calculated
field in the same query. How can I do this?

 

[Lynn Trapp]

Could you provide an example of what you want?

 

[Lynn Trapp]

Yeah, what Rick said.... <g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

I need to make a calculated field based on a calculated
field in the same query. How can I do this?

You can't. You need to include the entire first calculation inside of the second
one.

IOW if you have. . .

CalcOne: [Field1] * [Field2]

. . . and you want another. . .

CalcTwo: [CalcOne] * 100

. . .it won't work. But this will:

CalcTwo: ([Field1] * [Field2]) * 100

 

[Michel Walsh]

Hi,


With Jet, yes.

<rant>

With Jet, something like SELECT 0.07*Price As FederalSaleTax, (Price+GST) * 0.06 As
ProvincialSaleTax, ... FROM ...


With MS SQL Server, you can't proceed that way, since with the AllMighty, which is, it seems,
accordingly to the standard, aliases are not much more than caption for the presentation... even if
SQL is not a report writer. Anyhow, Joe Celko once suggested to "cut and paste" the definition of
the first alias:

SELECT 0.07*Price As FederalSaleTax, (Price+0.07*Price) *0.06 As ProvincialSaleTax, ...
FROM ...


which is error prone if you have to modify a buggy expression copied and pasted at many places, as
usual with cut and paste, but another solution is to embed virtual table ad nauseam, for each level
of "alias using alias", example gratuitously provided (else it may make no sense) :

SELECT FederalSaleTax, (Price+FederalSaleTax)*0.06 As ProvincialSaleTax, ...
FROM ( SELECT Price, 0.07*Price As FederalSaleTax, ... FROM ... )

since then, by magic, the caption is reusable. The problem is that AllMighty allows you to use, for
caption, an existing field:

SELECT toto As tata, tata As something FROM somewhere

and, so, if we re-use tata in another expression,

SELECT toto as tata, tata as something, titi+tata as tutu FROM somewhere

would tata refer to the original field, or to toto. For the AllMighty, it is more important to be
able to use an existing field as possible alias, than to be able to build complex arithmetic
expression, easily. In fact, you may find someone telling you that you do not use SQL to compute
arithmetic expression, do such stuff outside SQL !



Sure, another objection, justification from AllMighty to not support alias over alias is
"recursion": With initial fields being "a" and "b", consider

SELECT a+ toto as titi, b+titi As toto FROM ...

which is, basically, a system of 2 eq. with two unknowns: titi= a+ toto

toto = b+titi

or, in its "normal" form: 1.0* titi + -1.0* toto = a
-1.0*titi +
1.0* toto = b


but we are not interested in SQL solving that, ( or, for a real case, probably something
not even involving linear algebra). Like Jet does, producing an ERROR in that case is all what we
expect.

Anyhow, MS SQL Server does not, and I do not expect it would soon, support alias over alias,
and that is one of the reason I prefer Jet to MS SQL Server, since in my mind, what is important is
it to be able to do simple thing, in a simple manner, not to be able to define "caption" in
something not qualified as a report writer, in the first place. Virtual beer for anyone.

</rant>



Vanderghast, Access MVP

I need to make a calculated field based on a calculated
field in the same query. How can I do this?

You can't. You need to include the entire first calculation inside of the second
one.

IOW if you have. . .

CalcOne: [Field1] * [Field2]

. . . and you want another. . .

CalcTwo: [CalcOne] * 100

. . .it won't work. But this will:

CalcTwo: ([Field1] * [Field2]) * 100