calculating in query

H

hermie

I have a table with soccer results and want to calculate th e points of the
won lost or equal gamein a query.
Example: result portugal - holland 2-0 gives 3 points for portugal and 0
points for holland
portugal - holland 1-1 gives both 1 point

I want tocreate a calculated query that shows like:
portugal 4
Holland 1
etc. for others countries in the game

Someone knows how to do this?

Herman
 
M

Michel Walsh

Hi,



make a first query:

SELECT TeamA As Team, iif(ScoreA>ScoreB, 3, iif(ScorreA<ScoreB, 0, 1) As
Point
FROM myTable
UNION ALL
SELECT TeamB, iif(ScoreA>ScoreB, 0, iif(ScorreA<ScoreB, 0, 3)
FROM myTable


save it, say qu1.


SELECT Team, SUM(Point) As Total
FROM qu1
GROUP BY Team


should produce what you want.




Hoping it may help,
Vanderghast, Access MVP
 
H

hermie

Your example works great, only a parenthesis was missing and second select
must be scoreB>scoreA?

But thnx i have it working

herman
 
Top