Customer Reward Program design

[revvedmoto]

Ive asked this some what before but I think there was some confusion of what Im attempting to do.

Im trying to design a Rewards program for my retail store but I dont want the customer to rack up points over time. So I would like to implement an expiration on these points (create some kind of urgantcy to use there points). If there was no expiration date this would be simple math, Points accumulated minus Points used. Sense this isnt the case I need to some how keep track of the points and make sure when points are subtracted/redeemed that the're subtracted from the oldest points available.

Ive tried to find examples of ways to do this but I cant seem to find anything. I would really appreciate some help here.

[ItsMe]

Sounds simple enough. They use a point, a point gets subtracted. A point expires due to time, a point gets subtracted.

[revvedmoto]

Maybe Im making this more difficult then it has to be but I dont see how you could just remove points.
Lets say that if points arent used they start to expire on the 31st day. If on day one a customer earns 15 point, but on day 15 he uses 10 of those points. This leaves him 5 Points that will expire on the 31st. On day 31 I can't simply remove 15 points that were added day one because that would put is count in the negative.
Does this make sense?

[ItsMe]

Maybe you could dedicate a table to points. You could have a field that is of Yes/No data type, a field that is a timestamp, a PK that is of AutoNumber, and an FK of Long Integer to store the customer ID. Each record will represent one point.

When a client uses one of their points you can remove the oldest record by changing the value of the Yes/No field. When you query available points you can use the BETWEEN operator in the SQL statement to retrieve only the records Between valid dates and a WHERE clause to retrieve the correct FK and Yes/No fields.

[revvedmoto]

I guess I was hoping to find a more automated way of calculating the reward balance vs manually removing points from a particular date. The perfect situation all I would have to do is input a positive or negative number and it would do the rest.

[June7]

Just as you enter records for points used by customer, enter a transaction record for expiration of points. The trick is calculating age of points. This is like FIFO inventory. Not an easy concept to design. Review discussion in this thread https://www.accessforums.net/access/...ory-25249.html.

[revvedmoto]

Thanks june thats exactly what Im after. I tried to open their file but had some issues opening it in 2013. I can open the file and view the tables, queries, reports and macro but I cant edit or view any deeper then that. Anyone have any incite on this?

[ItsMe]

Not sure which DB you downloaded.
Perhaps this version that Orange posted is of use. Hold the Shift key down while the DB is first opening.
https://www.accessforums.net/access/...tml#post176618

[revvedmoto]

Thank you very much. I got pretty much everything taken care of I just revamped the Fifostock file to fit my needs. One last thing I haven't figured out is how to exclude dates older then 2 months. Any suggestions on how to do this? I would like to be able to Open the "Fifo Stock Report" and to have "Total Qty Available" show points that have yet to expire.