Can't use the alias???

[Guest]

This doesn't work out as I would expect (starts asking for parameters, then
returns the entire database). What's the deal?


SELECT C239.*,

[C239]![Mean]-[C239BigAggregates6]![C239AvgMean]/[C239BigAggregates6]![C239StDevMean] AS C239ZMean,

[C239]![Median]-[C239BigAggregates6]![C239AvgMedian]/[C239BigAggregates6]![C239StDevMedian] AS C239ZMedian,

[C239]![N]-[C239BigAggregates6]![C239AvgN]/[C239BigAggregates6]![C239StDevN]
AS C239ZN,

[C239]![Q1]-[C239BigAggregates6]![C239AvgQ1]/[C239BigAggregates6]![C239StDevQ1] AS C239ZQ1,

[C239]![Q3]-[C239BigAggregates6]![C239AvgQ3]/[C239BigAggregates6]![C239StDevQ3] AS C239ZQ3,

[C239]![Skewness]-[C239BigAggregates6]![C239AvgSkewness]/[C239BigAggregates6]![C239StDevSkewness] AS C239ZSkewness

FROM C239, C239BigAggregates6

WHERE C239ZMean >= 2
AND C239ZMedian >= 2
AND C239ZN >= 0
AND C239ZQ1 >= 2
AND C239ZQ3 >= 2
AND C239ZSkewness >= 0;

 

[Duane Hookom]

Replace the alias with the expression.

 

[Tom Ellison]

Dear Nicholas:

As Duane has told you, you cannot use the alias declared in the SELECT
clause in the WHERE clause.

You could, however, base another query on the SELECT . . . FROM . . .
portions of your query and filter in the WHERE clause of that query using
the aliases, since these would be the actual column and values with which it
deals.

SELECT * FROM (
SELECT C239.*,
[C239]![Mean]-[C239BigAggregates6]![C239AvgMean]/[C239BigAggregates6]![C239StDevMean]
AS C239ZMean,
[C239]![Median]-[C239BigAggregates6]![C239AvgMedian]/[C239BigAggregates6]![C239StDevMedian]
AS C239ZMedian,
[C239]![N]-[C239BigAggregates6]![C239AvgN]/[C239BigAggregates6]![C239StDevN]
AS C239ZN,
[C239]![Q1]-[C239BigAggregates6]![C239AvgQ1]/[C239BigAggregates6]![C239StDevQ1]
AS C239ZQ1,
[C239]![Q3]-[C239BigAggregates6]![C239AvgQ3]/[C239BigAggregates6]![C239StDevQ3]
AS C239ZQ3,
[C239]![Skewness]-[C239BigAggregates6]![C239AvgSkewness]/[C239BigAggregates6]![C239StDevSkewness]
AS C239ZSkewness
FROM C239, C239BigAggregates6
)
WHERE C239ZMean >= 2
AND C239ZMedian >= 2
AND C239ZN >= 0
AND C239ZQ1 >= 2
AND C239ZQ3 >= 2
AND C239ZSkewness >= 0;

In Jet, this may be too complex, but not in MSDE. So in Jet:

SELECT C239.*,
[C239]![Mean]-[C239BigAggregates6]![C239AvgMean]/[C239BigAggregates6]![C239StDevMean]
AS C239ZMean,
[C239]![Median]-[C239BigAggregates6]![C239AvgMedian]/[C239BigAggregates6]![C239StDevMedian]
AS C239ZMedian,
[C239]![N]-[C239BigAggregates6]![C239AvgN]/[C239BigAggregates6]![C239StDevN]
AS C239ZN,
[C239]![Q1]-[C239BigAggregates6]![C239AvgQ1]/[C239BigAggregates6]![C239StDevQ1]
AS C239ZQ1,
[C239]![Q3]-[C239BigAggregates6]![C239AvgQ3]/[C239BigAggregates6]![C239StDevQ3]
AS C239ZQ3,
[C239]![Skewness]-[C239BigAggregates6]![C239AvgSkewness]/[C239BigAggregates6]![C239StDevSkewness]
AS C239ZSkewness
FROM C239, C239BigAggregates6

Save this as, say, Query1, then:

SELECT * FROM Query1
WHERE C239ZMean >= 2
AND C239ZMedian >= 2
AND C239ZN >= 0
AND C239ZQ1 >= 2
AND C239ZQ3 >= 2
AND C239ZSkewness >= 0;

I favor this, not only because it eliminates a lot of duplicate typing, but
because it is also more easily maintained. Say one of the lengthy
duplicated expressions had an error. You have to correct it in two places.
I really don't like that. Avoid such duplication of code like the plague!

Tom Ellison

Replace the alias with the expression.

--
Duane Hookom
MS Access MVP
--

This doesn't work out as I would expect (starts asking for parameters,
then
returns the entire database). What's the deal?


SELECT C239.*,

[C239]![Mean]-[C239BigAggregates6]![C239AvgMean]/[C239BigAggregates6]![C239StDevMean]
AS C239ZMean,

[C239]![Median]-[C239BigAggregates6]![C239AvgMedian]/[C239BigAggregates6]![C239StDevMedian]
AS C239ZMedian,

[C239]![N]-[C239BigAggregates6]![C239AvgN]/[C239BigAggregates6]![C239StDevN]
AS C239ZN,

[C239]![Q1]-[C239BigAggregates6]![C239AvgQ1]/[C239BigAggregates6]![C239StDevQ1]
AS C239ZQ1,

[C239]![Q3]-[C239BigAggregates6]![C239AvgQ3]/[C239BigAggregates6]![C239StDevQ3]
AS C239ZQ3,

[C239]![Skewness]-[C239BigAggregates6]![C239AvgSkewness]/[C239BigAggregates6]![C239StDevSkewness]
AS C239ZSkewness

FROM C239, C239BigAggregates6

WHERE C239ZMean >= 2
AND C239ZMedian >= 2
AND C239ZN >= 0
AND C239ZQ1 >= 2
AND C239ZQ3 >= 2
AND C239ZSkewness >= 0;

 

[Guest]

Okay, I put the expressions back.....

Seemed like an okay idea to just use those aliases, after creating them
above. Guess not.

Thanks for your help, gentlemen.