trouble with inserting summerized field in query

[TjS]

Hellow everyone,

I have the following query:
SELECT Year, School, Number
FROM tblStudentNumber
GROUP BY Year, Number, School
ORDER BY School, Year

(quite simple

I want divide the field [number] with a sum over the numbers, within a school group...

Something like:
[number]/sum([number],"school")

and the second summerize should be with the year field
if the year is 2004, then number in 2004 minus number in 2003 for the school

something like:
([number], year=2004) - ([number],year=2003)

I could make select in select statements
(select year, number from tblStudentNumber where year = 2003)
(select year from, number tblStudentNumber where year = 2004)

and for the summerize:
(select sum(number) from tblStudentNumber where year = 2003 and school = 1)
(select number from tblStudentNumber where year = 2003 and school = 1)

but that would make a lot of select queries.

Please advise!

 

[jjturner]

Here is one approach you could use:

SELECT tMain.YearNum, tMain.SchoolID, tMain.NumberFld,
    (tMain.NumberFld)/(tSum1.SumNum) AS NumRatio,
    tSum1.SumNum AS CurrentYr_Aggregate,
    tSum2.SumNum AS PriorYr_Aggregate,
    tSum1.SumNum - tSum2.SumNum AS Aggregate_Diff
FROM tbl_Aggregate_Test AS tMain
  INNER JOIN
      ((SELECT YearNum, SchoolID, Sum([NumberFld]) AS SumNum
        FROM tbl_Aggregate_Test
        GROUP BY YearNum, SchoolID) AS tSum1
     LEFT JOIN
       (SELECT YearNum, SchoolID, Sum([NumberFld]) AS SumNum
        FROM tbl_Aggregate_Test
        GROUP BY YearNum, SchoolID) AS tSum2
     ON (tSum1.YearNum = tSum2.YearNum + 1)
     AND (tSum1.SchoolID = tSum2.SchoolID))
  ON (tMain.SchoolID = tSum1.SchoolID)
  AND (tMain.YearNum = tSum1.YearNum)
ORDER BY tMain.YearNum DESC, tMain.SchoolID ASC, tMain.NumberFld DESC;

(Note: Field names such as 'Year' or 'Number' are generic and as you can imagine, they duplicate actual names used in Access itself - which makes it difficult to avoid confusion, especially for you, the designer. So you may want to consider changing the field names (as I did in my example))

Another thing to note is that you're using GROUP BY on your [Number] field. I don't know why you're doing that, but I'm guessing that Number should NOT be grouped, so I provided my solution based on this assumption.

HTH,
John

 

[dunylym]

Cheers!
Valikie
Out of the many posts, this one attract my attention. I believe it is possible for anyone to participate.
Excellent ! I like it very much.