Multiple Select Statements with Calculations

[Lorlai]

I'm really going crazy here. I need to be able to update some data. Apparently this isn't possible with ways that I had posted before, so I'm going in a new way. How can I select multiple data so I don't need to have 3 different query tables? I've never understood the whole multiple selection thing.



ProductNo Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Type
12345667 .1 .3 .4 0 0 1 .2 14 .4 22 .22 7 FT
89070172 0 0 0 0 0 2 .5 1 .5 1 22 .3 PT
12345667 .3 66 7 0 0 0 12 .1 .2 0 0 0 PT
89070172 11 1 1 1 1 1 1 1 0 0 0 0 FT
68903421 0 0 0 0 0 0 0 0 1 1 1 1 FT

What I need is the average number of workers per quarter, grouped by project and Type. The way I've been doing it has been to create quarter averages by selecting them:

1.

SELECT Qtr1: (nz([Nov],0)+nz([Dec],0)+nz([Jan],0))/3, nz([Feb],0)+nz([Mar],0)+nz([Apr],0))/3 AS Qtr2, (nz([May],0)+nz([Jun],0)+nz([Jul],0))/3 AS Qtr3, (nz([Aug],0)+nz([Sep],0)+nz([Oct],0))/3 AS Qtr4

Which is saved as a query table called "Resources".

2. Then pulling the different types together and grouping by project:

SELECT Resources.ProductNo, Sum(Resources.Qtr1) AS AvgQtr1, Sum(Resources.Qtr2) AS AvgQtr2, Sum(Resources.Qtr3) AS AvgQtr3, Sum(Resources.Qtr4) AS AvgQtr4
FROM Resources
WHERE (((Resources.Type)="PT"))
GROUP BY Resources.ProductNo;

And saving this as a query table called "PT Resources".

Is there a way to do the above 2 steps in one single select query? Please help, I feel I may punch my monitor any second now!

[June7]

You can nest the queries. Would have to go to the SQL View editor and edit the second query. Copy/Paste the first query SQL into the second. Look like:

FROM (SELECT ...) As Resources