I am building a custom database for a law firm. I am rapidly approaching 60 tables, as it records everything you could possibly imagine related to plaintiffs, defendants, judges, courtrooms, filing dates, home addresses, business addresses, injuries, settlements, etc.
I was about done with the database, when they added a new wrinkle. For every single field in the database, they want to record the source of where the data came from. (Where did the first name come from? Where did the last name come from? Where did the address come from?) They want to be able to interact with this information during data entry, as well as having it show up on reports.
Short of adding an extra field for every piece of data in every table, does anyone have a good way to go about this?
I’ve experimented with creating a script that creates unique values in an unrelated table for every source value while doing data entry (if it doesn’t already exist) in a custom dialog, but then I can’t figure out a way to get those values to show up again next to each field when doing reporting.
Any help would be appreciated.