I would assume that ID in each of the fields other than Coverage are defined as auto-entered serial numbers? And the matching fields in Coverage are number fields?
It would appear that your database is doing exactly what you set it up to do, it's just not what you want it to do.
You have multiple one to many relationships with Coverage on the "Many" side of each relationship. That means that any record in Coverage will link to one one only one record in each of the other tables. But any one record in those other tables can link to any number of records in Coverage.
Thus, if you have a record in Coverage with 3 in the Client ID field, and you edit a client name field, all coverage records with 3 in the Client ID field will see this change. But records with any other value in the Client ID should not see this change.
If you are seeing the same value no matter what value is in the matching ID field in Coverage, I'd check that field's storage options in Manage | Database | Fields to make sure that it does not have global storage enabled.
Thank you. This is very helpful. Your description of the current set-up is correct. I checked the serial ID field of "Coverage" and it does not have "Global Storage"enabled. Are there other options I should consider or are there design changes I need to make? For example, should I have the serial IDs not repeat? Appreciate your help. Thank you
I don't know that you need to make any changes. I can see how your DB currently works, but can't tell you what behavior needs to change in order for it to work the way that you want it to. You'll need to describe the issue in more detail.
OK. Thanks. What I am trying to do is set up the coverage layout to allow me to add new entries or update existing ones without affecting other records (which is happening now because updates affect the static tables). I can provide more details with screenshots if that is helpful
I will assume that "entries" refers to records in Coverage.
But if two records in Coverage link to the same record in Clients, changing the data in a field from Clients SHOULD change this for both records. That's the inherent nature of the database design that you've used.
There are indeed cases where this is not desirable. In an invoice, for example, most businesses want to see the customer contact info that was current at the time the invoice was created. If they later get an update for this customer's info, they want it to show on new invoices but not the previously created ones. (Customer calls up: "why didn't Order #45678 arrive?", Business: "I see that the shipping address on that invoice is to 333 Main Street....")
If that's what you need here, then you should add fields to Coverage that copy data from fields in the related tables. This can be done by using field options to specify auto-enter calculations or looked up value settings. If you do that, then a subsequent change to data in a related table will not modify the data in existing records in coverage, only new Coverage records will show the change.