Calculate Percentages of Different Years on Report

[sxschech]

Must have my blinders on. I am converting an existing excel file to run as an Access report. Hope there is a simpler way to accomplish than what I've come up with.

Report should look like:
[tt]
Year
Compare G1 G1 G2 G2 G3...G8 GTotal GTotal
Read Writ Read Writ Read Read Writ
----------------------------------------------------------
Feb 05 492 483 477 507 460 1476 990
Feb 04 483 480 465 497 453 1401 977
Fall 04 483
Fall 03 468
..
..
Fall 95
-----------------------------------------------------------
Percent (Note:Fb=Feb; Fl=Fall)
Fb05 vs Fb04 1.86%
Fb05 vs Fl04 1.86%
Fl04 vs Fl03 3.20%
..
..
Fl96 vs Fl95
Fb05 vs Fl03 4.60%
-----------------------------------------------------------

[/tt]

Presently there are 8 Group Pairs, 10 years of data
Each month will change, the year range only changes once a year. Next month Feb(Fb) will become Mar(Mr), etc.
The formulae examples for the percent are:
(Feb05-Feb04)/Feb04
(Feb05-Fall04)/Fall04
(Fall04-Fall03)/Fall03

Source Data are as they show in the Top section of this Report example (In other words Data are not summed up or calculated by a query or the report).

What I have done so far is create a table to use in the query to generate a generic name (Feb-05 becomes CurYr, Feb-04 becomes PrevYr, Fall04 becomes Yr01..Fall95 Becomes Yr10). Then created 3 queries to do the calculation for one set of percentages.

Qry1 uses where YearComp_Code="CurYr"
Qry2 uses where YearComp_Code="PrevYr"
Qry3 joins Qry1 and Qry2 and then does the above calc.

Hopefully there is another way so that I don't have to create 31 queries (10 times 3 queries) Plus one big union query to use as the record source for the report.

 

[seaport]

I always believe that an Excel file is a much better reporting tool than an Access report. I use Access report only when it is a straight data output without any complicated calculation or formatting.

Considering your requirements in the message, I think you'd better make the report as an excel file and all the calculations be programmed in the excel file.

Hope this helps.

Seaport

 

[sxschech]

I finally got the report working as needed. I decided to forge ahead with the queries and discovered that I could do it in 3 queries rather than 31. I took out the Where Clauses in Query 1 and Query 2. Then I put a Where clause in the Query 3 to include only the year combinations I needed. I concatenated the YearComp field in Query 1 with the YearComp field in Query 2 to come up with the combinations I needed (shown below).

[tt]
In ("CurYrPrevYr","CurYrYr01","CurYrYr02","Yr01Yr02","Yr02Yr03","Yr03Yr04","Yr04Yr05","Yr05Yr06","Yr06Yr07","Yr07Yr08","Yr08Yr09","Yr09Yr10")
[/tt]