7 Replies Latest reply on Sep 19, 2012 8:42 AM by schamblee

Calculation question (I think)

Title

Calculation question (I think)

Post

I have a database for scoring a game that has two methods of scoring - a goal and a behind. Each time their is a score a record is created which then allows me to calculate a score for each team and then the margin between the two teams. My fields are the same but one set for the home team and one set for the away team with a margin calculation.

(sHomeGoal, sHomeBehind, cHomeScore, sAwayGoal, sAwayBehind, cAwayScore, cMargin=cHomeScore-cAwayScore)

What i am hoping is possible is that the running score and the margin is specific to the the time of the record being created. My problem is that when i enter a new score record it recalculates both the running score and the margin, which defeats what i am trying to do.

Many Thanks

• 1. Re: Calculation question (I think)

Here is a sample that might work for you.  It keeps a running total in "table view'.

• 2. Re: Calculation question (I think)

Tables in the database?   the expected tables would be

Team has many  games
Team has many scores
Game has two teams which means many
Game has many scores
Join tables connect tables that have many entities

Team --< Scores >-- Game

A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.
Follow these general steps to plan a database:
SNIP

Relational Database Design 101 (part 1 of 3): Designing a Flat File Database

Relational Database Design 101 (part 2 of 3)

Relational Database Design 101 (part 3 of 3)

The White Paper for FMP Novices is useful  -

• 3. Re: Calculation question (I think)

Thanks to both of you for your replies.  Either i wasnt very clear or I wasnt able to find the solution in your responses.

when i enter a new score record it recalculates both the running score and the margin,

The calculation is applied to all of the records rather that just that particular record.  What i want is something like below:

Goal=6, Behind =1

Record 1 - Team A scores goal, Running Score Team A = 6, Running Score Team B = 0, Margin = 6

Record 2 - Team A scores goal, Running Score Team A = 12, Running Score Team B = 0, Margin = 12

Record 3 - Team B scores goal, Running Score Team A = 12, Running Score Team B = 6, Margin = 6

What i am getting is:

Record 1 - Team A scores goal, Running Score Team A = 18, Running Score Team B = 6, Margin = 6

Record 2 - Team A scores goal, Running Score Team A = 18, Running Score Team B = 6, Margin = 6

Record 3 - Team B scores goal, Running Score Team A = 18, Running Score Team B = 6, Margin = 6

Here is a screenshot of what is happening with the portal

Can i get the calculations to "freeze" for each record and not update when a new record is added?

• 5. Re: Calculation question (I think)

It does not appear that you have selected the "running total" option for your running score summary fields--assuming that they are summary fields as they should be for this project.

• 6. Re: Calculation question (I think)

Thanks Phil - solved my problem beautifully

• 7. Re: Calculation question (I think)

Sorry.  Somehow the wrong sample file got attached to my post.