decrementing a pre-paid field?

[RonZucker]

I am building a database for the gym at which I work out in return for some personal training sessions. By and large, it's straight forward and easy. Clients table. Trainers table. Sessions table. Payments table. Pretty straightforward, with easy relationships and some forms.



HOWEVER...

Clients prepay for packs of training sessions. My initial design was simple. Have a rate field for the client (daytime appointments are cheaper than "prime time") and then simply divide the payment amount by the rate. At the end of each day, run a report, and do a mail merge for any client with 3 or fewer sessions remaining.

Unfortunately, rates change sometimes. For example, the rate for an hour session is going up by $2 on Jan. 1. This means that we can't keep the history. We need to create some field that decrements with each session, and that increments with payments. That is more complicated than I thought. I can't quite figure out how to automate that.

They're currently doing it with index cards. Which sometimes get lost. Some clients have been overcharge, and more have been undercharged. And then they have to write the emails for more payments by hand, so it doesn't happen as frequently or as regularly as it should. All of this should be fixable with a database, but I can't figure out how to do this.

Can anybody help? It seems like such a simple thing to do, yet I can't figure out how to do it well.

THANK YOU!

[ranman256]

you have a tRate table, that holds the current rate. When this changes, (or by date) it will update the tClient.Rate field.
All payments are recorded in the tPayment table, and updates the tClient.Balance field using the tClient.Rate field. (append qry)
Pre-paying adds amt to tClient.Balance field (update query)
Charging 'rent', deducts the Rate from tClient.Balance field. (update query)

[RonZucker]

huh. That makes sense. I had though of another field, but not another table. Thank you! I KNEW it was going to be simple. I appreciate it. This way, I just add a report on the Client table and send a mail merge where Client.Balance is less than or equal to 3*(Balance/Rate). Simple, clean and easy to keep current as the rate changes.

THANK YOU, ranman!