Compound Query?

N

Nicholas Kormanik

Okay, we'll break the problem into parts, hopefully solving one part at a
time.

As John suggested, it's necessary to have a separate query that handles the
aggregates. Here it is, Aggregates query:

SELECT Avg([Mean]) AS AvgMean, StDev([Mean]) AS StDevMean
FROM C239;

So, having this first query, we can proceed on to a second query:

SELECT C239.*, [Mean]-[AvgMean]/[StDevMean] AS ZMean
FROM C239, Aggregates
WHERE ((([Mean]-[AvgMean]/[StDevMean])>=2));

My question now is: Can I combine these two queries into a **single** SQL
process?

Thanks,
Nicholas
 
G

giorgio rancati

Hi Nicholas,
try this
----
SELECT C239.*, [Mean]-[AvgMean]/[StDevMean] AS ZMean
FROM C239,
(SELECT Avg([Mean]) AS AvgMean, StDev([Mean]) AS StDevMean
FROM C239 ) AS Aggregates
WHERE ((([Mean]-[AvgMean]/[StDevMean])>=2));
 
N

Nicholas Kormanik

Sooooo close, Giorgio!!!!

Your query formulation ran!! Looked great! But when I tried to save the
query results, the error message came up:

"Invalid bracketing of name 'SELECT Avg([Mean'.

What do you think??
 
J

John Spencer

Try entering the query again, but REMOVE all the "[" and "]".
Access does this type of query but it has troubles with the square brackets.

No guarantee, but it is probably worth a try.

SELECT C239.*, Mean-AvgMean/StDevMean AS ZMean
FROM C239,
(SELECT Avg(Mean) AS AvgMean, StDev(Mean) AS StDevMean
FROM C239 ) AS Aggregates
WHERE (((Mean-AvgMean/StDevMean)>=2))

That stands a good chance of getting changed to
SELECT C239.*, Mean-AvgMean/StDevMean AS ZMean
FROM C239,
[SELECT Avg(Mean) AS AvgMean, StDev(Mean) AS StDevMean
FROM C239]. AS Aggregates
WHERE (((Mean-AvgMean/StDevMean)>=2))
 
N

Nicholas Kormanik

Because of the unnecessary complexity, for now I've decide to leave the two
queries separate.

Thank you for you help on this, though, both Giorgio and John.

But another question follows......
 
Top