Results 1 to 6 of 6
  1. #1
    Liz is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    3

    Question Remaining sessions count- is this possible?


    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!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    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).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Liz is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    3
    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.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    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?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Liz is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    3
    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!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    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!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Count on ID
    By dssrun in forum Access
    Replies: 4
    Last Post: 07-26-2011, 11:45 AM
  2. Drop-Down list to display remaining text.
    By mnsemple83 in forum Forms
    Replies: 3
    Last Post: 07-18-2011, 10:29 AM
  3. Replies: 1
    Last Post: 06-16-2010, 09:25 AM
  4. Auto Calculating in remaining field.
    By aligahk06 in forum Forms
    Replies: 1
    Last Post: 05-16-2010, 01:06 PM
  5. Calculating "remaining budget $'s by project"
    By madcreek in forum Queries
    Replies: 3
    Last Post: 04-28-2010, 12:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums