SQL: group after group

[novo]

I have lots of saved linked queries (about 20 pieces) wich makes lots of transformation with data. For example, after the groupping action is done by one field it should be acted on the base of another field with some filters . As I`ve seen in MS help NESTED QUERIES wich could be supposed to be helpful in deciding this task are destined for a little bit another use. They are used for filtering data on the base of sql statement from multiple sources - not for consecutive data manipulating.

So, if the following transcription doesn`t work:
SELECT f1, f2, f3 FROM (SELECT f1, f2, f3), -

may I build only one big sql statement instead of weave lots of little ones?

(sorry for my english)

 

[Pat Hartman]

Nested queries are separate querydefs. The inner query is created and saved as a querydef. Then the next outer query uses the saved query instead of a table and manipulates it. If you have another level, then that query uses the middle saved query and manipulates it. I'm not sure how deep the nesting can go, I've gone 5 levels.

Your example, is trying to use a sub-select which can only select a single column and so would not work for your purpose.

 

[MSAccessRookie]

SELECT f1, f2, f3 FROM (SELECT f1, f2, f3)

Whenever I have tried to Nest a query like this one, Access likes to reformat it and gets the intention incorrect, causing it to fail. As a result, I do my nested queries as literal nested queries (I have had up to 7 deep).

Example:
 
To get the same result as 
 
[COLOR=deepskyblue][B]   SELECT f1, f2, f3 FROM [/B][/COLOR]
[COLOR=deepskyblue][B]       (SELECT f1, f2, sum(f4) As f3 [/B][/COLOR]
[COLOR=deepskyblue][B]       FROM Table1 [/B][/COLOR]
[COLOR=deepskyblue][B]       GROUP BY f1, f2) [/B][/COLOR]
[COLOR=deepskyblue][B]   ORDER BY f1[/B][/COLOR]
 
I would do the following:
 
[COLOR=black][B]Query1:[/B][/COLOR]
 
[COLOR=deepskyblue][B]   SELECT f1, f2, sum(f4) As f3 [/B][/COLOR]
[COLOR=deepskyblue][B]   FROM [COLOR=black]Table1[/COLOR][/B][/COLOR]
[COLOR=deepskyblue][B]   GROUP BY f1, f2[/B][/COLOR]
 
[B]Query2:[/B]
 
[COLOR=deepskyblue][B]   SELECT f1, f2, f3 [/B][/COLOR]
[COLOR=deepskyblue][B]   FROM [COLOR=black]Query1[/COLOR][/B][/COLOR]
[COLOR=deepskyblue][B]   ORDER BY f1[/B][/COLOR]

I just now noticed that Pat Hartman has already tried to explain this fact.

 

[novo]

Access likes to reformat it and gets the intention incorrect, causing it to fail.

Thank you guys for your help - my worst apprehensions are being fulfilled...
The critical quantity - about 45 subsequent saved Querydefs. After this treshold Access refuses to work justifying oneself that it could not open so many tables)))
But I found the rest as I`ve realized that it`s not correct to propagate such a quantity of queries because it will lead to the instable access work.
So one way - is operating with temporary tables and it doesn`t please me - it`s so boring ...

 

[Pat Hartman]

45 nested querydefs sounds excessive. What are you trying to do?

 

[novo]

45 nested querydefs sounds excessive. What are you trying to do?

It`s rather simple - you have the initial common timereport table collected from employees. So, you should (0) ghroup it by project/division, (1) you should remap some project`s time inside divisions, (2) remap some employees for all department, (3) calculate the extratime in section of project/dept.

Really I undestand that everything because of my lazy. To build 45 queries simplier and quicker than have tortures around the model of coefficients calculated in the code