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

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the SUM() aggregate function:

SELECT student_id, SUM(WeightedResult) / SUM(weighted) As
WeightedResultPct
FROM (
SELECT student_id, weighted, (weighted * result) As WeightedResult
FROM table_name
WHERE < some criteria? >
) As A
GROUP BY student_id

Substitute your table name for "table_name." If there is some criteria
be sure to put it in the WHERE clause, else remove the WHERE clause in
the inner query.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSjpHK4echKqOuFEgEQLCugCfQhT8qEKTfWrzLbqVmG3fHdTbnUYAoJte
bAF9hgrMRG2fQwCts5XLoZM/
=KsDK
-----END PGP SIGNATURE-----

 

[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,