I have a list of hospital names that I want to store inside a hospital table:
However, these hospitals need to be displayed as a drop-down list as three different fields inside my parent table, Parent. Note, for this example I'm not actually using my full table Parent for simplicity.
Each hospital field A, B, C represent their own information (to the user) but I do not want to create redundancy by making two other tables with the exact same data as Hospital; these fields need to contain the same data as Hospital because they're the same pool of hospitals.
What I've tried thus far:
- Making a new table HospitalA, HospitalB and referenced the Hospital ID inside the Hospital table (because they all should be equal). I created a value list that lists the values from Hospital name stored inside Hospital ID from Parent. However, if I make this the same for the other hospital fields, picking one value from the drop-down list changes ALL others values in those fields.