You're probably not going to like this answer, but I'm going to suggest to you that you remodel the database to split out your sessions into a separate table. Each session should be a separate record. (This is an example of database normalization; you might see that term mentioned here and there on the forum.) You can script it in such a way that the user can't create or delete any of them, so the specifying of the number of sessions is done up front and can't be changed, but by holding your sessions as separate records, you solve the issues you're having.
Here's why: Each session becomes a snapshot in time because it's a unique record. Nothing changes on that record when you enter information in a different record. Each session is a separate entity or "thing" in the database, and by making them separate records, you prevent them being blended with one another.
Hope that helps. Please don't be shy about asking any questions.
Sounds like you just need to change the field so it's not a calculation, but rather a number. Then however you are creating the record for each session, when you create that new record, calculate the value and stick it in that field.
We'd probably need more details on how you're currently calculating that field's value to make a more precise recommendation.
Kind of sounded like she already had a "clinical notes" table that was playing the role of a "sessions" table. Unless they are entering in more than one "note" record per-session. Given how the counter currently works though, it sounds like the count of sessions is coming from a count of records from somewhere though, so I'm not sure a complete remodel is needed.
Thanks for the incredibly quick response. If I understand correctly, we do have it set up so that each note is it's own unique record. The field itself (# of remaining sessions) pulls from a "Patient" table, while each note is in a "Visit" table. Perhaps this is the issue..... All other fields in the note, but certain fields we "pull in" from the patient table. For example, the patient's last name is pulled in from the patient table, and appears in all of the records in the visit table. If we were to change the patient's last name after 10 visits, it would change those 10 historical visits to the new last name as well. As I type this, I am realizing the larger scale of the issue, as I wouldn't want that to happen either.
So, what I've got are certain fields that exist in the patient table, that change in the visit (note/ record) each time they are updated. Is there a way to create a reference to what existed in the patient table at the time the note for the visit table was created? Hopefully that makes sense....
Okay, I guess I misread the issue. But if you do have it modeled such that each visit is a unique record, it's relatively easy to get a "remaining number" of visits set up. Depending on when you want that value to update, what I would suggest is that you script the decrementing of the value based on those criteria. For example, if you don't create the visit record until after the visit is completed, you can just write a script that creates the new visit record and decrement the value as part of that script. Or, if you have some status field or the like that indicates, "We're done!", you can just add decrementing the count to a script that closes the visit.
As far as freezing the value of a field, you can do that by echoing the data down to the related table into a separate field via either a lookup (simpler) or an auto-enter calculation (more flexible). Which one you use will depend on when you want the value to update. A lookup updates based on the relationship between the tables, which usually means it updates when the related record is created and any time the key field or fields are touched (which is usually never). An auto-enter calculation can be updated based on changes in other fields. Of course, you can always write a script to refresh the field under whatever conditions you want.
I'd have a field called "Number of Visits" in the "Patients" table. This is where the "20" would go.
Then I'd have a field called "Visit Number" in the "Sessions" table. This would start with "1" for the first visit, "2" for the next, etc. These could be auto-entered as = Max ( "Visit Number" ) + 1, as seen from the "Patients" table.
Then in the "Sessions" table I'd have a calculation field called "Visits Remaining" = "Number of Visits" – "Visit Number".
Thanks, guys! Incredibly helpful (and so quickly!)- I am fairly positive these suggestions solve the issue, but as things go, I got pulled off in another direction momentarily. I'll try these out this afternoon and let you know what worked best.
Thanks again for all the help!
(How did people do their jobs before the internet existed?!!)
We banged our heads against the keyboards for six or seven days until an answer magically appeared.