Remaining sessions count- is this possible?

[Liz]

Hi,
I'm a personal trainer and new to access. I'm trying to set up a database for my clients and I have a question (I'm not even sure what you would call this or if it is even possible, but I'm hoping so).



Here's my situation:

My clients will buy x number of workout sessions from me when they first sign up. What I'd like to do is input that number of sessions in a box on the client information sheet and then as I enter the date and time of each workout as it happens, have the program subtract that session from the total number of sessions left.

So if Bob has 12 sessions, and we train on Monday and Tuesday, is there a way to have access show that Bob now only has 10 sessions left? Oh, and Bob can also buy more sessions at any time, so I would need to be able to add 5 more sessions onto his running total and have it subtact his workouts from the new number (17-2=15 in this case) without screwing things up.

Is this possible in access? A way to automatically count the number of records and then subtract it from a number that I have inputted? If so, how?

Thanks for your help!

[pbaldy]

What you're describing is essentially the same as an inventory application's "quantity on hand". I've purchased 10 widgets and sold 3 of them, so I want to show 7 on hand. In that instance, the common recommendation (and mine) is to calculate the on hand from transactions rather than try to maintain a field that stores that value. In your case, you would presumably have a transaction where the person bought x number of sessions, and transactions where they used each session. Their remaining sessions can be calculated from those values (sum of purchased sessions less sum of used sessions).

[Liz]

Ah-ha! That is exactly what I am talking about! Thank you Paul! Now, is there a way to do this calculation for everyone in my database at the same time (and maybe show it a report)? I have hundreds of clients, and doing each calculation for each person one at a time would take forever. What I'd like is to come in on Mondays and run a quanity on hand report that shows every client and how many sessions they each have left.

[pbaldy]

Oh, I wouldn't be doing anything by hand, so counting for everybody would be as easy as counting for one. All the summing I was talking about would be done by Access, not by you. What's your table structure? Generally the transactions are done one of two ways: one table for all transactions (positives and negatives), or separate tables for purchases and sales (usage in your case). I don't have a strong feeling for either method, and we can do what you want with either. If you have a database started with some sample data, can you post it here?

[Liz]

I haven't started my database yet...still getting the feel for my parameters, but I have a feeling it would be two tables, linked by the client name. I'll try to get something started and then post a sample on here. I'm sure this won't be the last question I have about it!

[pbaldy]

No problem Liz, post back when you have a db to work with and we'll get it working for you. Welcome to the site by the way!