# [AccessD] complex query!!

Pedro Janssen pedro at plex.nl
Fri Jan 21 13:08:54 CST 2005

```Hello Steve,

thanks for the time and help with my question.
Although you do not fully understand the use of the last StDev question, i
very grateful that you helped me. It saves me a lot of time.

Pedro Janssen

----- Original Message -----
From: "Steve Erbach" <erbachs at gmail.com>
To: "Access Developers discussion and problem solving"
Sent: Friday, January 21, 2005 12:41 AM
Subject: Re: [AccessD] complex query!!

> Pedro,
>
> Add another column to the Results table, StdDev Number, Single.
>
> Change INSERT query from original method:
>
> strSQL = ""
> strSQL = strSQL & "INSERT INTO Results "
> strSQL = strSQL & "  ( AvgUsing, AverageVal, StdDev ) "
> strSQL = strSQL & "SELECT '" & strTblName1 & "-' & [" & strTblName1 & _
>    "].[KeyFld] & ':" & strTblName2 & "-' & [" & _
>    strTblName2 & "].[KeyFld] AS AvgUsing, "
> strSQL = strSQL & "  ([" & strTblName1 & "].[Value]+[" & _
>    strTblName2 & "].[Value])/2 AS AverageVal, "
> strSQL = strSQL & "  ([" & strTblName1 & "].[Value] - [" & _
>    strTblName2 & "].[Value])/2 AS StdDev "
> strSQL = strSQL & " FROM [" & strTblName1 & "], [" & strTblName2 & "];"
>
> The "StdDev" calculation is simply half the difference between the two
values.
>
> The Cartesian Product you want for a single table I think can only be
> derived by stepping through the table in code. I don't believe it can
> be done in a query.
>
> Steve Erbach
> Neenah, WI
>
> On Thu, 20 Jan 2005 22:52:05 +0100, Pedro Janssen <pedro at plex.nl> wrote:
> > Dear Steve and Others,
> >
> > nice that you all are talking about age and children. I am not that old,
> > only 40 year. I also have a son and he is 4 years old. All nice, but i
won't
> > puss you, but have you thought about my original question??
> >
> > I also have a new one, that's also important. I need a Cartesian query
of a
> > table with one field.
> > And need each average of pears of records , but not with itself
> >
> > I have:
> >
> > Table1
> > fieldA
> > 1
> > 2
> > 3
> > 4
> >
> > I need Average of:
> > 1 vs 2
> > 1 vs 3
> > 1 vs 4
> > 2 vs 3
> > 2 vs 4
> > 3 vs 4
> >
> > I'll hope that this is easy.
> >
> > - Pedro Janssen -
> --
> AccessD mailing list
> AccessD at databaseadvisors.com