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...)
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.
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.