3 Replies Latest reply on Jan 17, 2012 9:27 AM by philmodjunk

    Keeping track of sessions per client

    andy@the-wod.co.uk

      Title

      Keeping track of sessions per client

      Post

      I‘m fresh back to data base construction after quite a number of years and the memory is weak.

      What i am looking to achieve is:

      A client purchases a number of sessions with me (a Personal Trainer). I need a way to enter the total numbert of sessions purchased and each time I enter a session record that pulls in the client record it can look up the sessions booked, deduct 1 from the total and then record that in the session record and also store it in the client record for use the next time the client record is pulled into a sessions record.

      Any help will be greatly appreciated.

      Thanks

      Andy

        • 1. Re: Keeping track of sessions per client
          philmodjunk

          Are all "sessions" the same for all clients or do you have different kinds of "sessions"?

          Seems like you need one table for clients, one for purchassing blocks of sessions and one for booking those sessions. Updating a field in the booking table (where I'd have one record for each session) could record each time a client "uses" a session. (Don't know if you bill for booked session where the client is a no show or not...)

          • 2. Re: Keeping track of sessions per client
            andy@the-wod.co.uk

            Hi.

            Thanks for the feedback.

            I think I’m getting there.

            What I have at present is:

            A table for clients
            A table for each workout
            A table for body weighing

            I also have a table, “client sessions”, that brings together generic information from all three. It has two lists containing:

            Date and result for each workout
            Date and result for each weighing

            Each workout is payed for and a no-show is still charged. I think you are right I also need a table recording what has been purchased.

            I would like it to work so that I would enter into the new “sessions purchased” table a record with date, cost and number of sessions bought. Then, when I create a new “workout session” record it can take the “number of sessions brought” value from the “sessions purchased” table and subtract ‘1’ from that value. It would then enter it into the “sessions purchased” record. 

            Process would be:

            “sessions purchased” table would contain a value of ‘13’

            A new “workout session” record would take that value of ‘13’ subtract ‘1’

            It would then display the result in a field labeled “session number”.

            Visually this would look like this:

            Session number 12/13

            The complicated part is now. When I create another “workout session” record for that client I need to take the new value of ‘12’ subtract 1’ from that number to give the result ‘11/13’. Each resulting “workout session” record will reduce the result until the result would reach ‘1/13’.

             

            If anyone can help me understand where to start with this I would greatly appreciate it.

            Thanks

            Andy

            • 3. Re: Keeping track of sessions per client
              philmodjunk

              Let's start with a basic relationship:

              clients::ClientID = SessionBlocks::ClientID

              This allows you to set up a layout based on Clients with a portal to SessionBlocks where you can record each block of sessions that a client has purchased.

              Then we add a second relationship:

              SessionBlocks::SBlockID = WorkOuts::SBlockID

              This calculation, defined in SessionBlocks will tell you how many unused sessions exist for a given SessionBlocks record:

              SessionsPurchased - Count ( WorkOuts::SBlockID )

              The trick here is that you link each WorkOuts record to a specific SessionBlocks Record, which in turn links it to the client, instead of just linking it to a client record directly. You may want to set up a field on your workouts layout formatted with a drop down list of clients and use a script trigger on it to look up the oldest SessionBlocks record with unused session for the specified client and then copy its SBlockID value into the new WorkOUt record. Note that this approach requires that you create a SessionBlocks record even if you have a client walk in and just wants to pay for a single session on the spot.