calculating in query

  • Thread starter Thread starter hermie
  • Start date Start date
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
 
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
 
Your example works great, only a parenthesis was missing and second select
must be scoreB>scoreA?

But thnx i have it working

herman
 
Back
Top