Help with Control Source calculation in report

[Guest]

Problem in accuracy of my report.......
For 1 of the textboxes in my report I'm trying to get a total average for
each applicant...
I thought in the query I could list all the years and averages...and do a
count on Start Years (CountofStartYear) to get # of years of study...
So my Control Source for that textbox would be:
=Sum([Average])/Sum([CountOfStartYear])

That is fine for the applicants who do not have the years broken up into
smaller subsections. (just 2000, 2001, 2002...etc) but when you have
applcants who list the Start Year several different times it doesn't add up...

In the example below [CountOf StartYear] would equate to 6 years...when I
want
Sum([CountOfStartYear]) above to be only 3 years!
The sum of the averages is fine...but how do I fix this part to reflect an
accurate # of yrs of study to be divisible by???


StartMonth StartYear End Month EndYear No of Courses Average
1 2000 6 2000 5
90
7 2000 12 2000 5
85
1 2001 12 2001 10
80
1 2002 4 2002 2
90
5 2002 8 2002 3
85
9 2002 12 2002 3
90

 

[Duane Hookom]

Create a group by query that selects records grouped by applicant and year.
Then create another query based on the previous query that counts the year
grouped by applicant. This should result in a totals query of applicants and
the number of years.

Add this last query to your report's record source so that you have a count
of years for each applicant.