By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,218 Members | 1,326 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,218 IT Pros & Developers. It's quick & easy.

Query on Formula

P: n/a
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

nk*******@gmail.com




Nov 15 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
MLH
Just put your mathematical formula in a textbox control
on a form - in a way that it will evaluate to a numeric value.
Then, for the criteria in your query, just type
Forms!Formname!Controlname That should do it.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Mon, 14 Nov 2005 17:51:28 -0700, "Nicholas Kormanik"
<nk*******@yahoo.com> wrote:
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

nk*******@gmail.com




Nov 15 '05 #2

P: n/a

I couldn't get that suggestion to work.

Again, here's the problem:

The following would be a 'standardized' column for X:

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

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

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

Thanks,
Nicholas

nk*******@gmail.com

*** Sent via Developersdex http://www.developersdex.com ***
Nov 15 '05 #3

P: n/a
MLH
If you post your SQL, it would be more likely to
attract some attention as the mathematical
expression alone reveals only a fraction of
your whole objective.
Nov 15 '05 #4

P: n/a
Nicholas Kormanik wrote:
I couldn't get that suggestion to work.

Again, here's the problem:

The following would be a 'standardized' column for X:

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

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

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

Thanks,
Nicholas

nk*******@gmail.com

*** Sent via Developersdex http://www.developersdex.com ***


1) Show rows where (X - Avg(X)) / StDev(X) > 0
2) Show rows where X > (X - Avg(X)) / StDev(X)

Let's warm up with a two query solution to each:

tblX
ID Autonumber
X Double
ID X
1 -3.13
2 2.15
3 2.77
4 3.32
5 5.08
6 4.31

qryMSD:
SELECT Avg(X) AS Mean, StDev(X) AS StandardDeviation FROM tblX;

!qryMSD:
Mean StandardDeviation
2.41666666667 2.91395035418702

qryPositiveStandardX:
SELECT X FROM tblX WHERE (X - (SELECT Mean FROM qryMSD)) / (SELECT
StandardDeviation FROM qryMSD) > 0;

!qryPositiveStandardX:
X StandardVarialbe
2.77 0.121255783519315
3.32 0.310002993714476
5.08 0.913994066338989
4.31 0.649747972065765

Hint: Don't run this when all the values of X are the same :-).

qryXPastStandardX:
SELECT X, (X - (SELECT Mean FROM qryMSD)) / (SELECT StandardDeviation
FROM qryMSD) AS StandardVariable FROM tblX WHERE X > (X - (SELECT Mean
FROM qryMSD)) / (SELECT StandardDeviation FROM qryMSD);

!qryXPastStandardX:
X StandardVariable
2.15 -0.091513798882502
2.77 0.121255783519315
3.32 0.310002993714476
5.08 0.913994066338989
4.31 0.649747972065765

Now that we're warmed up:

qryNewPositiveStandardX:
SELECT ID, X FROM tblX WHERE ([X] - (SELECT Avg(X) FROM tblX)) /
(SELECT StDev(X) FROM tblX) > 0;

!qryNewPositiveStandardX:
ID X
3 2.77
4 3.32
5 5.08
6 4.31

qryNewXPastStandardX:
SELECT ID, X FROM tblX WHERE X > ([X] - (SELECT Avg(X) FROM tblX)) /
(SELECT StDev(X) FROM tblX);

!qryNewXPastStandardX:
ID X
2 2.15
3 2.77
4 3.32
5 5.08
6 4.31

James A. Fortune

Nov 15 '05 #5

P: n/a
Something like

SELECT X
FROM YourTable
WHERE X>(X-(DAvg("X","YourTable")/DStDev("X","YourTable")))
--
Terry Kreft

"Nicholas Kormanik" <nk*******@yahoo.com> wrote in message
news:li***************@news.uswest.net...
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

nk*******@gmail.com



Nov 15 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.