Calculating a Bonus based on "effective date"

[mpreston14]

I have a database created to rank soccer referees. Each match they officiate they get a certain number of points that are calculated by various criteria. In addition to the actual match points, if they pass a fitness test they earn a certain bonus% on their points. I have worked that into the calculation just fine for the refs that did the test at the beginning of the season, but the summer test just happened and I cannot think of a way to apply it only on matches after the test was taken. Any ideas?

Here are the calculations I am using now:

1) Raw data imported and filtered for matches/missmatches:



Points: [Match].[MatchMultiplier]*[League].[LeagueMultiplier]*[Position].[PositionMultiplier]*[Finals].[Bonus]

2) To rank the referees by total points including the bonus I then use:

Total Points: Sum([Officiated_Matches_T].[Points]*[Referee_T].[FitTestBonus])

Now, I know I shouldn't really store the calculated points in the database, but as this is the first time we are doing something like this it was easier to play with and validate. Eventually I would like it to be calculated only at the query/report level.

So if a referee earns 50 points for a game last week and they did the fit test this weekend and earn a 10% bonus, and next week the do the same match they get 55 points. Their total would then be 105. My current way of doing this would give them 110 points for those two matches.

I appreciated your help!

Mike

[June7]

Will ref have only one fitness test? Somehow get the test date into query or on form/report by query join or DMax so the date is available for use in expression:

IIf([GameDate]>[TestDate], ...

I am not sure about your calculation, don't seem to be adding the bonus amount.

[mpreston14]

Thank you, I will try the If statement with the dates!

I run two separate queries which is how I get the bonus applied. It does work, but its not a clean as I would like.