Average of 3 values

[flaviooooo]

Hey,

this seems like an easy task, but I can't see the solution:

I have 3 separate ratings for a player, from 3 different news papers. Now I would like to compile an average rating per player.

I have PlayerID, HBVL_Rating, LDH_Rating, VM_Rating

Beware, sometimes one of the ratings is not filled in...

Any help would be appreciated!

 

[dhookom]

I would normalize the table with a union query:

=== quniRatings ====
SELECT PlayerID, HBVL_Rating as Rating, "BHVL" as NewsPaper
FROM tblNoNameGiven
WHERE HBVL_Rating is not null
UNION ALL
SELECT PlayerID, LDH_Rating, "LDH"
FROM tblNoNameGiven
WHERE LDH_Rating is not null
UNION ALL
SELECT PlayerID, VM_Rating, "VM"
FROM tblNoNameGiven
WHERE VM_Rating is not null;

You can then create a simple totals query that averages [Rating] by PlayerID.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]