I have been writing a file to run my office and need help with a routine to count down the number of authorized session for a given patient. I have had no trouble assigning a value of 1 to each session and then subtracting that from the number of sessions allowed. The problem arises when the allowed sessions are used up and a new authorization is granted for the same patient. I have tried linking the calculations to a serial number I have given the authorization grant but no matter how I do the calculations the prior sessions end up subtracting from the new set, whereas the process has to start over. I do not want to erase the old data as it can be important to retrieve at times. I know I am close to the answer but can’t quite get it. Can anyone point me in a direction?

There are many ways to go here ...

The simplest (considering what you already might have in place) is that when a session you've created (which exists with a 1) is 'used' the value is changed to 0. This would depend upon you setting any Session field. You would then use Sum(thatRelationship::thatField) instead of Count(). You didn't say what calcs or process you were using (or how Grants or Patients are related currently to Sessions) so I'm winging it here a bit.

Also that 1/0 (number hopefully) might instead be set to an Auto-Enter via calculation (Replace) so that when the Completed Session Date is entered (do you have one?) then it toggles to 0. Also, when the record is created it can Auto-Enter DATA = 1. This will create a self-sustaining process. But 1s and 0s are a bit limited. You might consider using something like the PatientID instead.

I hope you have (3) tables: Patients, Grants and Sessions. They each would contain their own uniqueID (something like PatientID, GrantID, SessionID) as auto-enter serial (increment by 1) or some other system-generated key. Then your Sessions would have a text field (GrantID) and relate to Grants. In this way, the proper sessions will always relate to only their Grants. And aggregate functions Sum(), Count() will be isolated to only their sessions. Count() of the Sessions::GrantID would provide the 'contract total number of sessions' and Sum(Sessions::thatNumberField) would provide number of remaining sessions.

Without knowing how you determine session completion, this is about the best I can suggest. If you explain more, maybe we can expand a bit ...

LaRetta