I am developing a Health Records database for my Optometry Practice, and would like to address the following issue:
It is very common to see multiple members of the same family. It is simple enough to create and duplicate records initially to ensure a match on the address (and other data in common). However, as families move, or insurance changes, I would like to have a easy way to update everyones' common info...without doing a search for all people in that family, and manually updating a single field and then copying it to that found set. While this method works adequately well it is open to user error (especially if they copy a field across a incorrect found set).
Ideally, I would like to have a "Acct Responsible" field that is simply a self-join using the primary key as a foreign key. Then once that is populated, have it pull the data from the individual that is referenced as the "Acct Responsible" and place that persons address, phone, etc in a second set of fields.
This would allow me to potentially have a different address for children as they go off to college, but still have the parent info on their file; or to just create a copy/paste script to change all the data to match the “Acct Responsible” info on the patient record.
Any suggestions would be greatly appreciated. If there is a more functional or elegant way to address this issue I am VERY open to suggestion.
P.S. Have attached a file that hopefully makes it more clear as to what I am trying to accomplish. It works fine if you relate back to the same record, but can't get it to work when trying to relate to some other record.