StDev Query

[Zack Barresse]

How would I do a query in Access to find do a Select Query for all records
in a table which the it calculates the Standard Deviation * 3 of only those
location values (Location is a field) specified which are checked against
each other? I'm sure this sounds confusing so if more information is needed
please let me know what you'll need (I'm sure there will be).

I can do this in Excel. Would it be easier to port this information to
Excel and check that way?

 

[Michel Walsh]

Hi,


The WHERE clause is always evaluated BEFORE the aggregate/group, but using
the designer is a little bit tricky since with a Total query, the criteria
is, by default in the HAVING clause, clause which is evaluated AFTER the
aggregate/group. You have to use, in the designer, in the line Total, use
the "aggregate" WHERE ! That is the way to get a WHERE clause, through the
query designer.

It may be preferable to do it "by hand":

SELECT location, StDev(someNumericalField)
FROM somewhere
WHERE locationType = someType
GROUP BY location


Hoping it may help,
Vanderghast, Access MVP

 

[Zack Barresse]

Thank you Michael,

I appreciate you getting back to me. Sorry it took me so long to respond,
had some major reports to do. I will test what you have shown me here. I'm
still learning SQL so it's all a little vague to me right now, but I'm
getting the concepts down.