Compound Query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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));
 
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??
 
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))
 
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......
 
Back
Top