Query based on calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Seems so simple.....

Show rows where:
X > (X -Avg(X))/StDev(X)

How does one do this in Access? I've spent hours, and can't get it.

Thanks,
Nicholas
 
I got my question post a little wrong. Here it is corrected:

The following would be a 'standardized' column for X, one field:

(X - Avg(X))/StDev(X)

I want to see all the rows where that resulting standardized value is
greater than, say, zero.

What's the easiest way to do this in Access?

Thanks,
Nicholas
 
Nicholas said:
I got my question post a little wrong. Here it is corrected:

The following would be a 'standardized' column for X, one field:

(X - Avg(X))/StDev(X)

I want to see all the rows where that resulting standardized value is
greater than, say, zero.
Click to expand...


Create a calculated field in the query using your
expression, then set its criteria to >0
If you do not want to see that column in the query's
result, then uncheck the Show box.
 
Here's what I tried:

SELECT ([Mean]-Avg([Mean]))/StDev([Mean])
AS [StdMean]
FROM [C239]
WHERE [StdMean] > 0

Here's the latest error message:

"You tried to execute a query that does not include the specified expression
'([Mean]-Avg([Mean]))/StDev([Mean])' as part of an aggregate function."

What do I do next?

Thanks.

Nicholas
 
Chris, I appreciate your effort to help. Yes, I could put more info about
the actual problem, but I don't think that's necessary. The problem as
outlined seems ludicrously simple, so simple I feel like crying. Trying to
get Access to cooperate has been one of the most frustrating experiences I've
encountered in the computer world.......
 
You need to use a GROUP BY for all values that aren't stratight aggregates
of the data. Possibly something like the following. Access won't use the
alias so you need to restate the calculation in each place it is used.

SELECT ([Mean]-Avg([Mean]))/StDev([Mean]) AS [StdMean]
FROM [C239]
WHERE ([Mean]-Avg([Mean]))/StDev([Mean]) > 0
GROUP BY ([Mean]-Avg([Mean]))/StDev([Mean])

However, I don't think that is going to fix the problem for you, it may run
but I suspect the results may be zero records returned.

I think that you will probably need a query to get the AVG and StDev for the
entire set and then use that to calculate your stdMean in another query.
 
Back
Top