Help with calculations in query!

[jacqueline]

I am creating a report based on a query...part of the report is to find the
total average for all years of study by each med school applicant.

For example: Applicant 1
Start Yr - 2000
# of courses - 10
Average - 85

Start Yr - 2001
# of courses - 8
Average - 90

and so on...

So now I am trying to use these fields to...
3) total the # of yrs of study per applicant (2 yrs in this case)
1) get the total # of courses taken for all study years. (18 for this
applicant)
2) get the overall average for all study years combined. (87.5)

Not sure if there are any Null fields...so would want to eliminate those as
well.

Thanks!

 

[Michel Walsh]

Hi,



something like:



SELECT applicant,
Sum(NumberOfCourse),
SUM(NumberOfCourse * Average) / SUM(NumberOfCourse),
Max(startingYear)-Min(startingYear) + 1
FROM myTable
GROUP BY applicant






Hoping it may help,
Vanderghast, Access MVP

 

[jacqueline]

Thanks Michael for helping...

I am trying to build expressions in the query for each of these fields.
(maybe I should be doing it at the report level??

Basically...if I was to pull Number_Of_Courses, Averages and Years_Of_Study
out of the table and into the query...
I would see more than 1 record for each applicant...depending upon how many
years of Study they had...

So for each yr of study they would have a matching # of Courses for that
yr...and an average for that year.
If Applicant 1 went to school in 2001, 2002, 2003, 2004...I would see all
these years listed under Start Year (which by the way is a TEXT field in the
table...not a date field)..

The report list ALL applicants names...and for each applicant want to see
the Overall Average (Sum of Averages /Total number of Yrs of
study)...where/how do I get these values...in the report...=
Sum([Averages])/Count([Years_Of_Study])????

Thanks!

 

[Michel Walsh]

Hi,


For a report, I will try to assign a control to an expression like:

=SUM( ... )

where the aggregated expression but the SUM (or other aggregate) is a know
field that belong to the same report-group. Such a bound control is
typically found in a footer of a report defined group (and sort).

So if you have, as groups, for your report, Applicant, and, as
sub-groups, Starting Year, then, in the footer of the group Applicant, I
would try to make the control source equal to such a formula. Take a look at
report "Invoice" in Northwind, at the OrderID Footer, for the control
InvoiceSubtotal



Hoping it may help,
Vanderghast, Access MVP

Thanks Michael for helping...

I am trying to build expressions in the query for each of these fields.
(maybe I should be doing it at the report level??

Basically...if I was to pull Number_Of_Courses, Averages and
Years_Of_Study
out of the table and into the query...
I would see more than 1 record for each applicant...depending upon how
many
years of Study they had...

So for each yr of study they would have a matching # of Courses for that
yr...and an average for that year.
If Applicant 1 went to school in 2001, 2002, 2003, 2004...I would see all
these years listed under Start Year (which by the way is a TEXT field in
the
table...not a date field)..

The report list ALL applicants names...and for each applicant want to see
the Overall Average (Sum of Averages /Total number of Yrs of
study)...where/how do I get these values...in the report...=
Sum([Averages])/Count([Years_Of_Study])????

Thanks!

Hi,



something like:



SELECT applicant,
Sum(NumberOfCourse),
SUM(NumberOfCourse * Average) / SUM(NumberOfCourse),
Max(startingYear)-Min(startingYear) + 1
FROM myTable
GROUP BY applicant






Hoping it may help,
Vanderghast, Access MVP