[AccessD] complex query!!

Hello Gustav,

its a wonderfull idea to use the same table again and include the Where
statement with not equals.

But i get strange average results.
When the table has 3 records values are 2 ,    3    , 4
The average should be;
(2+3)/2 = 2,5
(2+4)/2 = 2
(3+4)/2 = 3,5

i get avg = 5,5   ,   5   ,    6,5


When i try to use:

SELECT (([A.Factor]+[B.Factor]) /2)  AS Average12

i get an error: You tried to execute a query that does not include the
specified expression.

Any Ideas?

Pedro



----- Original Message -----
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Friday, January 21, 2005 2:25 PM
Subject: Re: [AccessD] complex query!!


> Hi Pedro
>
> Not quite sure what "1 vs 2" means, but if you include the table twice
> in a query you have your Cartesion product on one table. Thus, it would
> be along this route:
>
>   SELECT
>     Avg(A.Factor + B.Factor) AS Average12
>   FROM
>     tblTable AS A,
>     tblTable AS B
>   WHERE
>     (A.Factor <> B.Factor);
>
> Again, the will leave one record only; if you wish a result for each
> record, you'll have to Group By on something.
>
> /gustav
>
> >>> pedro at plex.nl 20-01-2005 22:52:05 >>>
>
> .. 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.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>