Thank you for your post.
I'm not sure where you are entering the "Tissue specific information". In another sentence, you say "There is also a similar primary key/foreign key relationship for Organisms in the Tissue table as well."
Let's assume you want a drop down menu that only shows the Organism name when you type in a key field.
Once you set your relationship between the two tables, go to "Layout Mode" (View menu) and double-click the Organism field. This brings up the Field Control/Setup window. Change the "Display as" drop down menu from Edit Box to Pop-up Menu. Next, click on the "Display values from" drop down menu and select "manage Value Lists..."
At the next dialog box, click "New". Give the Value List a name (not important), and check the option "Use values from field". Click on the "Specify field..." button, and click on the "Use values from first field" drop down menu. Choose the Tissues table and select the Organisms field froom the list. Be sure to select "Include all values", and then exit by clicking OK to each open dialog box.
You can now go to Browse mode, and when you enter a Tissue ID, only those Organism values for that Tissue ID will be displayed.
Ok, that helped. I have actually been able to do that. I guess I am just thinking about the data structure a bit differently. Thank you so much for your timely response.
Ahhh, I just noticed one problem with this solution. If you update the name of an Organism in the organism table....the update is not automatically done in the Tissue table.
How do you get the change of an organism name to cascade to all of the other tables that might have this field?
"Ahhh, I just noticed one problem with this solution. If you update the name of an Organism in the organism table....the update is not automatically done in the Tissue table."
It sounds as though your database isn't as normalised as you think. ;)
The Tissue table should be displaying the Organism name field from the organism table.
It is normalized. :P That isn't the problem.
If I create a record (i.e Record A) in the organism table, and use the pop-up menu that I have created in the Tissue table to select one of the records...that works just fine. I end up with a Tissue table that has tissue specific information and a selected Record A for the Organism. So everything seems fine up to this point.
However, if I find out that Record A in the Organism table is actually mispelled, and should read Record AB. If I modify the Organism Name in the Organism table to say RecordAB, the corresponding Organism Name field in the Tissue Table does not get updated to say RecordAB, it will still read RecordA.
That is what I am trying to figure out how to fix.
Why wouldn't the updated field in one table cascade change to all the related tables in the database? I have been looking online and such to find an answer and I haven't yet. Any suggestions?
It sounds like you are trying to change the key field. If the key field is changed, then you lose the link to any tables that use that key field. You will need to change the key field in each of those tables to restore the link.