[AccessD] complex query!!

Hi Pedro

Bob is right but you need to Group By on the fields too:

  SELECT 
    Avg(tblGroup1.FieldA+tblGroup2.FieldB)/2 AS Avg2
  FROM 
    tblGroup1,
    tblGroup2
  GROUP BY 
    tblGroup1.FieldA,
    tblGroup2.FieldB;

/gustav

>>> pedro at plex.nl 12-01-2005 20:29:14 >>>
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