[AccessD] complex query!!

Steven W. Erbach serbach at new.rr.com
Sat Jan 15 08:36:02 CST 2005


Pedro,

Since it's an INSERT query you'd have no place to put the values unless you added an extra field for the Standard Deviation. The reason for the INSERT query, of course, is that you can't do all of those queries and have a single result set using only SELECT statements.

It looks like your suggested query is intended to calculate a standard deviation on each result, is that right? Doesn't sound like something very useful. What you want is, say, the standard deviation of the averages for each set of averages, AB, AC, AD, etc. So that would be 21 different standard deviations, yes?

Sounds like you want a new query to act on the Results table:

SELECT 
  StDev(Results.AverageVal) AS StDevOfAverageVal, 
  Left([AvgUsing],1) & Mid([AvgUsing],InStr([AvgUsing],':')+1,1) AS StdGroup
FROM
  Results
GROUP BY
  Left([AvgUsing],1) & Mid([AvgUsing],InStr([AvgUsing],':')+1,1);

That will give you a 21 row recordset with the standard deviations for each group of averages.

Steve Erbach
Neenah, WI

> ------------Original Message------------
> From: Pedro Janssen <pedro at plex.nl>
> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
> Date: Fri, Jan-14-2005 4:31 PM
> Subject: Re: [AccessD] complex query!!
> 
> Hi Steve,
> 
> never doubt yourself, doubt microsoft.
> 
> I suddenly realised that i also need the Standarddeviation for the 
> results
> of the averages.
> Can i change the code of the sql in:
> 
> strSQL = strSQL & "  (StDev[" & strTblName1 & "].[Value]+[" & _
>                strTblName2 & "].[Value]) AS StDevVal "
> 
> or is it better to ad an extra field in the results table for the StDev 
> and
> adjust the code?
> 
> Pedro Janssen
> 





More information about the AccessD mailing list