I work for a community development corporation, and am developing a database to store information on businesses, individual properties, and residents/ partners/ misc. individuals. I have organized my base into three tables with the primary fields: Addresses, Business Name, and Contact name. The idea is that sometimes we want information on a property (state of repair, vacancy, etc), sometimes we need info about a business (sector, ownership demographic, etc), and other times we need info on specific people (if they are residents, touchpoints, contact info, etc.). We also want to know how these different sections interplay. For example, if I am looking up a property, I would also like to see what business is in it and who is the business owner and who is the property owner (if we have already collected that information).
Now, for my problem: I want to make it easy for people to add info to this base without having to update each table every time. For example, if I talked to a Jane Doe, a manager of XYZ Tattoo at 1111 N Way, I could go to the “Contacts” table, add Jane’s name and contact info, and select (or add) XYZ Tattoo from the “Business info” linked table field and 1111 N Way from the “Addresses” linked table field. However, when I tried this, the linked information is not updating across all tables. Specifically, while the info in Jane Doe’s record is correct, with the linked fields updated, when I go to the “Business Info” Jane Doe has not populated the linked record field “Contact Name”. To make it more confusing, she has populated the “Contact” field in the "Address record. See Screenshots.
I would like two outcomes, First, for the “contact” field to be updated on the related “business” and “address” records on their perspective tables when those records are selected on the linked record field in the contact table. Second, it would be ideal if, by associating the business and address within the contact record, the business and address would then be associated in their perspective tables even though I did not go in and associate those records in each of those tables. That way, I don’t jjhave to update multiple tables everytime I get new information
Any ideas? Thanks in advance!