Special Calculation Exclude Certain Years

[sxschech]

Several Issues. I'm not sure if this is an Access 2003 issue or If it has been a while (was using 2000 but have had a break for a few months) since I've done this. I have been having a problem with Alias on report. I created a calculated text box called txtYr2Sum in the detail section. In the Group Footer Section I created another text box and put in =sum(txtYr2Sum), When I run the report I get a prompt, when I put in the actual field name for txtYr2Sum it is ok, there are other fields that I refer to an item by its "txt" alias and it works ok. That is the simple problem, the more complicated problem is that I need to perform some averages. This I can do, however, they want to exclude the current year and the previous year. In the case of the report the Current Year is 2005 and Prev Year is 2004. The beginning year is 1993. In one col we calculate years 1993 through 2004 and in other only through 2003. Thus we are dividing by 12 in one case and by 11 in another. I was trying to set this up with out hard coding so that as the years change, if they decide to go back earlier or a different time period the formula could do the division for averaging accordingly. At present, I either get no data or after playing around, get an error message: Multi Level Group by clause is not allowed in a subquery. (I had added a field in the query to show max year, count of years to which I could then subtract 1 or 2. Example 1993 to 2005 is 13 years. 13-1=12 and 13-2=11. We were then trying to use sum(TotalItemsGroup1)/(TotalYrs-1) to get one average and sum(TotalItemsGroup2)/(TotalYrs-2) to get the other average. Hope this is clear enough. And thanks.