MS Access: Logic For Point/Miles Rewards Program

Hi Everyone:

I am new to this forum as I recently started working in MS Access. I’m hoping someone can please help me with this query I’m trying to do using MS Access 2007.

This task is somewhat similar to a typical points/miles rewards credit card program.

The problem is as follow:

For every $2,000 of accrued purchases in a credit card, an individual will get a gift card.

So, any remaining balances over $2,000 will rollover and be added to next set of transactions, until it reaches $2,000 again. For instance, if on the first week I spent $2,500, I would qualify for my first gift card. The excess $500 would rollover and be added to the next set of transactions until they reach $2,000 again. Therefore, this would qualify me for another gift card.

So far, I’ve created two tables, one that stores accrued purchases (Promo History) and another table based on an Append Query which takes all qualified transactions from the first table and appends those records to the second table (Promo History Qualified) in order to track those qualified transactions over $2,000.

However, I’m stuck on how I would accommodate the remaining balances to be carried over. Any suggestions would be greatly appreciated!!

Thank you in advance,

marel