Compound Query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nicholas Kormanik - 15 Nov 2005 20:03 GMT

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

Reply to this Message

giorgio rancati - 15 Nov 2005 20:30 GMT

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));
----

bye

Signature

Giorgio Rancati
[Office Access MVP]

> Okay, we'll break the problem into parts, hopefully solving one part at a
> time.
[quoted text clipped - 16 lines]
> Thanks,
> Nicholas

Reply to this Message

Nicholas Kormanik - 15 Nov 2005 21:50 GMT

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??

Reply to this Message

John Spencer - 15 Nov 2005 22:06 GMT

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))

> Sooooo close, Giorgio!!!!
>
[quoted text clipped - 4 lines]
>
> What do you think??

Reply to this Message

Nicholas Kormanik - 15 Nov 2005 23:48 GMT

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......

Reply to this Message