Create a Query from 2 queries

[Greaser]

Hi,
I want to create a query from 2 calculated queries.
Query1 contains several rows
yearNo 'year part of the date of birth
numBirths 'number of births for that year
logNumBirths ' natural log of numBirths

Query2 (from Query1) contains 1 row
n: Count(*) 'count number of records in Query1
sumX:Sum([YearNo]) 'sum of all years in Query1
Mx:[sumX]/[n] 'sum of years/number of years
sumY:Sum([logNumBirths]) 'natural log of sum of all years
My: [sumY]/[n] 'sum of natural log of years/number of years

I want to create a query that uses data from both queries:
Txx = ([YearNo1]- [Mx])^2 + [YearNo2]- [Mx])^2...[YearNo_n]- [Mx])^2)
Basically, for each row of Query1, I want to use data from Query2. Or is there another method I can use?

Any help would be appreciated.
Thanks,john

 

[stephenk1973]

Save both queries (e.g. Q1,Q2) and select from them without linking

SELECT
Q1.yearNo, Q1.numBirths, Q1.logNumBirths, Q2.n, Q2.SumX,Q2.Mx, Q2.SumY, Q2.MY
FROM Q1, Q2;

Probably easiest to do this in query designer, once you got it working you can should be able to get you Txx.

Regards

Stephen

 

[PHV]

You want something like this ?
SELECT Sum((YearNo-Mx)^2) AS Txx
FROM Query1, Query2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Greaser]

Worked like a charm.
Thanks.
Cheers,
John