Extract Current Balance

[mikajake]

I am trying to add a table to my Bingo database that tracks Jackpots for certain games. Every session, the pot grows until it hits either 5000 or 10000 depending on the game. When it does, there is no pot growth until it is won. How do i keep a running balance and then pull that balance into my main table so that when I enter a new record it shows the jackpot balance.

Thanks

[mikajake]

Should I clarify this post?

[fseipel]

It's hard to give a specific answer not knowing the structure of the database.

Assuming there is a table related to a second table on a key field such as "Game ID #", the SUM function could be used to add up the running jackpot total for a given "Game ID #". The related records could be shown in a portal or not displayed as you see fit. The relationship will allow summing all related fields (e.g. individual jackpots, jackpot adder per bingo ticket).

So the jackpot would be SUM(table::field)

I'm not sure what dictates the max. jackpot; let's say it's a relationship to "Game ID #" and is called Maximum_Jackpot. Then a CASE statement can compare the jackpot to the max; if the SUM exceeds Max, it is set equal to max; otherwise, it's set equal to the sum;

CASE(SUM(table::field)>Maximum_Jackpot;Maximum Jackpot;SUM(table::field))

I'm not sure if this goes in a script or a calculation field because I don't know enough about the requirements.

This may be overly complicated; if all tickets add the same amount to jackpot, then a COUNT function may suffice rather than a SUM.

[fseipel]

I spent 10 min and created an example of what I discussed in previous message. Fields are included for Ticket Number, Adder_to_Jackpot, Game_Number (assuming all tickets belonging to the same jackpot, have an identical game number ID), and running_jackpot (all numeric). A single self-join relationship of game_number::game_number allows for calculation of a running jackpot total. When a new ticket is entered, the adder to jackpot for that ticket is input, along with game number; upon exiting game number field a script trigger executes the script, commiting the record (so that current record counts towards total), and then calculates the sum. If the sum is < $5,000 it is set to $5,000, otherwise it's set to the sum of individual jackpots for all tickets with that game group number. If running total field is already populated script won't update it again (e.g. if you tab through db and script triggers again). This will probably need modified e.g. if all tickets have the same adder to jackpot, to use different maximum jackpot based on game type, etc to meet the specific requirements (you mentioned both a $5,000 and $10,000 case). You can also use a calculation rather than a script trigger if desired.

Bingo.zip

[mikajake]

wow! looks neat, I will have a look at it tonight.

thanks

[mikajake]

Ok looks like what I need. However, this is what happens, if you look at the screen shot I sent, the growth amount is a calculation..no problem, I can do that in the Jackpot table, but how do I get the current balance to stay current with each day / session...there are 3 sessions each day, each in our case a progressive game.

Follow me?

Mika

[mikajake]

oops! the screen shot.

Screen 1

[fseipel]

Pardon my ignorance of how the games work, but it would seem you could include a timestamp field for each game, perhaps auto-input. This way, you can calculate a jackpot only on games between a certain range of timestamps (times with date). The range might be input in two global fields (start, end timestamps). The relationship could be individual game timestamp >= global_game_start_timestamp (or use two relationships if an endtime is also req'd). You could then do a recalc each time a new ticket is purchased; calculated jackpot could go in a global field also.

If this is inconvenient or overkill, you could just use game ID # as it is now; however, have it match on a *range* of game ID's that you input into global fields, so if you wanted totals for game ID's 3 thru 6, you'd enter those and the relationship would match against them. Each new session would start a new game ID (morning, afternoon, evening, etc or however that works). Again if you always total only to present, a single relationship on game id > game id input in global field would suffice.

[mikajake]

If you look at the screen shot, the 66 tickets means 66 people played. TED 4 strip - 8 purchased and the Special - 235 Played. These are merely arbitrary numbers as what matters is the pot growth, it is a percentage of the revenue. In the twoonie pot, the jackpot can be won at any time...no maximum, it just resets when it is won. The bingo file you sent me works great, problem is I can't get it to add the seed amount when the pot is won. Seed amount is the amount to start the next pool. So all jackpot work the same, there is a current balance, that when won starts at zero. A seed amount is added to start the pool. There has to be a prizes paid field as we need to track the amount of prizes in the month paid.

If you would like I can send you an email with the program for you to have a look.

Sorry about this, Jackpot tracking is the most important aspect of this program.

Mika.

[fseipel]

Hi again,

Regarding these additional requirements: Can you post an example file?

To add the seed amount, can't it just use the function SUM and add a fixed amount to it?

It sounds like a field for game type is needed; the calculation can then use a CASE statement to determine if jackpot ceiling has been reached for that game type, and whether a fixed seed value should be added to the SUM function, and what the ssed amt is for that game type.

You should be able to add a fixed seed amount just by adding it to the formula, i.e. $1,000 seed + SUM(...) -- ideally, seed, jackpot ceiling, etc would go in a related table so they could be easily edited.

I would put the prizes paid in another table, separate from tickets, and relate the two tables if necessary. I'm assuming, each prize entry should have at minimum game type, amount, and timestamp.

When a jackpot has been won, I'd suggest you require they push a button, using a Show Custom Dialog to prompt "Are you Sure", and if they answer yes, changing the relationship so that the SUM only operates on games purchased after the jackpot. This might be achieved using a timestamp for each ticket, or serializing each ticket so that, say, the relationship sums only tickets for game id 2 AND ticket ID number > 115. Thus, the relationship would match, and thus sum across only, tickets of a certain game type AND after a certain date or ticket serial number, thus, excluding tickets from before jackpot was paid out.

Alternately, script can do a find for all tickets of a given game type, and set their jackpot contribution to zero after a payout. Then they won't contribute to the sum. Script should also go to the 2nd table and create a prize entry.

Another option would be to include a normalization parameter to add to the jackpot; initially set at zero. So jackpot = normalization parameter + seed + sum(..). After payouts, normalization would be set so that jackpot = seed. Solving for normalization, then, seed cancels out on both sides, and we have normalization = -sum(..), set when jackpot paid. This eliminates need to change the relationship. Since -sum and +sum cancel out, jackpot = seed initially. Or if you relate jackpots table to main table, you can subtract the sum of all jackpots from total, thereby normalizing it to equal seed initially.

Edited August 6, 2009 by Guest

[mikajake]

can I email you the program

[Lee Smith]

You can attach a File here.

I expained the process in this recent Thread Link

[mikajake]

People I work for do not want me posting it in a public forum...that is why I asked.