How do I create a running total

[dniezby]

Okay, I hope I can clarify this correctly.

Im designing a poker tournament and membership manager and I don't know how to approach a feature I need.

I have a table -EVENTS that contains the core event data like the date, location and attendance.

Then I have a separate table that links to the event's KEY and is used to store the events results. Player name (pulled from member table), finish position and points. Players are awarded points for where they finish a game. I want those points to be added to The members overall point total.

Any thoughts on how I add these points to a total and where do you think I should store the running total? Should it always be calculated in a query or stored in a field? I'm so lost right now any help would be greatly appreciated.

[June7]

Don't store running total, this should be calculated. Not sure what you mean be 'added to members overall point total'. How is overall point total determined?

A running total in a query is not easy.
http://support.microsoft.com/kb/290136
http://www.techrepublic.com/article/...-query/6140569

Textbox on report has a RunningSum property.

[dniezby]

Imagine this. Think like your in a Dart league. For every game you play, the player gets 20 points. For every place that player takes he gets additional points. Let's say our player took 1st today, Took 3rd yesterday and 5th on Tuesday. He was awarded 400 points for the first play, 140 for third and 90 points for the 5th place. Now after each one of those "events" my user will select the player by name and enter their finish position along with points (unless I can figure out how to calculate those as well) Anyway, after each event, that total will be added to the player's total pool of points. When a player looks up their account, it should show "Your current point total". They will then use those points like currency; so I will have to subtract points from them as well.

[June7]

Each of those places is a separate record in table?

Not sure what you need is running sum, might be you need an aggregate calculation. Aggregate calcs can be done with a Totals query using GROUP BY criteria. Can also build a report using Grouping & Sorting features with aggregate calcs in group sections.

If data saved is finish position then the points could be calculated, assuming the point value for each position is standardized.

If you want to provide db for analysis, follow instructions at bottom of my post.

[dniezby]

June7, thanks for your input.
The points players earn when playing games can be used to purchase perks. So, when the points are awarded in the event, they need to also be added to the "player's total" When a player looks up their account, they should see all of their additions and the points they've paid out.

Here is a copy of my application with some dummy info in it.

Obviously, it's still in the designing phase so I don't have all the forms I will eventually be using. But here is a typical flow through the system.

A new player comes to the league, we go to Member Registration Form.

We're going to start a new game, we open, the Event Form.
When the game is over, we enter the players finish positions, select or enter their ID number, then enter (eventually automatically entered) player point awards.

Those totals get added to the player's overall grand total.
The player goes home, looks at his / her account and see x number of points

Hopefully the database will make things a little more clear.

[June7]

Since ID field in Player table is the primary key, the Player field in event_results table must be number type, not text. Then set link between them in Relationships.

Ideally, would not save aggregate data. Calculate the net difference of points awarded and points used. This means two aggregate (Totals) queries with grouping on player ID - one to total points earned and one to total points used. Then join those two queries to the Players table. Create a field in query (or do calcs in textbox on report) that subtract used from earned.

I don't see a table for transactions of points used.

You might consider not saving ID value as pk/fk for the lookups, such as the player_Status. Since the status descriptor is short, I recommend just saving the text descriptor. This eliminates lookup aliases and joining tables in queries to retrieve the descriptor. I doubt this will cause db to get too large.

Recommend no spaces in names (tables, fields, queries, forms, reports).