Problem with if / sum
I've set up a database where players of a team and the goals they scored are stored. On every player record all matches the team played are shown (from another db called Games). Besides each match I have a (repeating) field Goals where I put the number of goals the particular player scored in that particular match. Besides that I want fields called "Total amount of goals National Competition", "Total amount of goals Competition" and "Total amount of goals International Competition". In the db Games I have a field Competition in which is filed in what competition the particular game is played (National, International or Friendly).
I defined the field Total amount of goals Friendly as follows:
Field name: Total amount of goals Friendly
Options / comments: Indexed, If ( Games::Competitie="Friendly" ; Sum (Goals) ; "0" )
Something is wrong here because all goals are counted no matter what competition they are scored in.
Can someone point out what I am doing wrong?
More about my db and the reason I use a repeating field here, can be found in this Insert one db into another. Help needed..