[AccessD] complex query!!

Hello Rob,

i know its a Cartesian query. I used one in the past (from Gustav Brock),
but that was a total different query and situation. In the help i couldn't
find an example of a normal Cartesian query.
Your query only gives me one result.  I need every single average of the 4
calculations.

let me explain a little bit better.

i have

tblGroup1
FieldA
2
3

tblGroup2
FieldB
4
5

I need

QueryAverage
"avg(2+4 /2) ="   3
"avg(3+4 /2) ="   3,5
"avg(2+5 /2) ="   3,5
"avg(3+5 /2) ="   4


I'll hope this explaines a little more.


- Pedro -




----- Original Message -----
From: "Bob Hall" <rjhjr at cox.net>
To: <AccessD at databaseadvisors.com>
Sent: Wednesday, January 12, 2005 4:39 PM
Subject: Re: [AccessD] complex query!!


> On Wed, Jan 12, 2005 at 01:51:38PM +0100, Pedro Janssen wrote:
> > Hello group,
> >
> > for a statistic analyses of DNA-samples i need to compare each sample
from a group with eachother sample from another group. I have 7 groups with
up to 17 samples. Let me explain what i need in an example.
> >
> > I have
> >
> > Two tables group1 and group2, with each two records.
> >
> >
> > i need the average of:
> > group1_record1 + group2_record1
> > group1_record2 + group2_record1
> > group1_record1 + group2_record2
> > group1_record2 + group2_record2
> >
> >
> > and this for 7 groups up to 17 records
> >
> >
> > Is this possible in a query per 2 groups,
> > or can this been done in total by code?
>
> Pedro, your example is a little ambiguous. Assuming that you want to add
> records and take the average, and not add columns and take the average,
> the query is a simple Cartesian product. Assuming a column in group1
> named First and a column in group2 named Second:
>
> SELECT Avg(group1.First + group2.Second) AS Mean
> >From group1, group2;
>
> If you had tables with thousands of records, this would take forever to
> run. But as long as you've got less than 100 records in each table, it
> should be OK.
>
> I apologize if I misunderstand what you're trying to do.
>
> Bob Hall
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>