[AccessD] complex query!!

Steve Erbach erbachs at gmail.com
Fri Jan 21 13:53:52 CST 2005


Gustav,

I tried that two-table thing, too, but it didn't give Pedro what he
wanted. With his example of 4 records in the sample table, the number
of results should be 6. Using that sample table twice as you suggest
gives 12 results:

1 vs 2,  2 vs 1,  3 vs 1,  4 vs 1
1 vs 3,  2 vs 3,  3 vs 2,  4 vs 2
1 vs 4,  2 vs 4,  3 vs 4,  4 vs 3

It looks to me like he'd have to use code to cycle through the table
n-1 times (n being the number of rows) to get the averages for pairs
of values, similar to the code I gave him for cycling through pairs of
tables. what he'll get isn't exactly a Cartesian product, it's
something else. Can't think of the name for it.

Steve Erbach
Neenah, WI

On Fri, 21 Jan 2005 14:25:13 +0100, Gustav Brock <Gustav at cactus.dk> wrote:
> 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



More information about the AccessD mailing list