[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 -