Optimize performance

[jasminewhite]

Ultimately, I want to pass through the entire DB once and produce a set of outputs broken into 3 parts, instead of writing 3 SQL that pass through the DB 3 times to produce 3 separate sets of results.
MyTABLE:
GROUPfield FYfield VALfield (whole #)
1 3QFY11 1
1 4QFY11 2
1 1QFY12 1
1 2QFY12 1
1 3QFY12 1
1 4QFY12 1
1 FY13 2
1 FY14 2


2 3QFY11 1
2 4QFY11 2
2 1QFY12 1
2 2QFY12 1
2 3QFY12 1
2 4QFY12 1
2 FY13 2
2 FY14 2
The DB has several groups like above
I want the query to display the following:
FY11 report:
GROUPfield VALUEfield
1 sum(all FY11 VALUEfield of GROUP 1)
2 sum(all FY11 VALUEfield of GROUP 2)
continue for all groups…….
TOTAL FY11 sum of the above sums
FY12+ report: (report on all fiscal years, except FY11), same format & details as above

GRAND TOTAL report: (the sum of 2 TOTAL lines from the above 2 reports,
GROUPfield VALUEfield
GRAND TOT sum of totalFY11 and totalFY12+
(is also total of all records in DB)

My PROBLEM: I could have query1 produce FY11 report (filter on FY11), query2 produce FY12+ report (filter on everthing except FY11), and query3 produce GRAND TOTAL report (no filter). This will hurt performance, I have to pass through the DB 3 times. If I write a C++ program, I could just pass through DB once, save each set of result in separate place holders then produce the report at the end. How do I achieve this via SQL and/or any other ACCESS mechanism? I guess I cannot use values from another query’s fields?

Any suggestion would be greatly appreciated.