I'm not sure if there is something similar on here anywhere, but I thought I'd ask.
So the base(s) are pretty complex at this point, however here it goes.
We have different levels of tracking.
High level tracking of Clients. This tab(le) stores client data that goes through various states and tracks them. They may never go into training, but it stores other high level data.
Once a client has paid/agreed, etc for training they move from #1 tab into this tab: this table tracks unique training, so company ABC from #1, could have ABC-110123, and ABC-011723 (start date of training as unique ID). This also stores lookup data from the next one as far as ALL the training
This table is the actual training "calendar" so all the training dates with the respective courses, date & time, etc are store here. We also track the #of attendees that are invited as well as who actually shows up.
All of this data is used to track the amount of training, specific people who attend, etc. The biggest thing for this discussion however, is how to setup a proper "Billing" tab(le), that will allow for updating to pricing without affecting previous training for each client. So in other words, the table would have to be able to store the "old" pricing, and who paid that, but then also still have the ability to update the pricing without changing those clients that trained for that specific period when the price was whatever it was.
I'd already started something up, but not sure if this will work either. Any pointers would be helpful.
A given training at one time is always one price, is that correct? Why don't you just have a record for each training + date? (With the primary field being Training Name (Date provided). You could duplicate a training record when it's offered again or have a separate training by date table. That seems like the best solution to me, but I may be missing something.
Another option would be to have the "price" to be billed be pasted through an automation when a bill is created rather than be a lookup. That way, no old records would be affected by a price change.