weight query

[inungh]

I have data like following

Student ID, Class Id, weighted, Result
1 1 10 70
1 2 3 40

and would like to have the report like following

Student ID, FinalResult
1 (70 * 10 + 40 * 3) / (10 + 3)

The formula will be ((result1 * weighted1) + (result2 * weighted2)
+ ......) / ( weighted1 + weighted2+ .....)

The only thing I can think of is to iterate the table and strore in 2
varaiables and do calcuations between 2 variables.

I just wonder can I use query to get final results.


Your help is great appreciated,

 

[vanderghast]

SELECT studentID, SUM(result*weighted) / SUM(weighted)
FROM somewhere
GROUP BY studentID


in SQL view.


Vanderghast, Access MVP

 

[inungh]

    SELECT studentID, SUM(result*weighted) / SUM(weighted)
    FROM somewhere
    GROUP BY studentID

in SQL view.

Vanderghast, Access MVP













- Show quoted text -

Thanks millions for the information and helping,