Creating a table with fields connected to multiple hierarchies... Oh boy... See post...
Hello and thanks for taking a look...
I am creating an archaeological database that has a hierarchy ranging from Site all the way to Individual Objects. The problem I am having, is getting the Individual Objects table to be "flexible" in displaying/ auto-filling information from one table or another dependant on it's specific attributes.
So, at the top of the hierarchy, we have the Site table. After this comes the Sub-site table (a specific region within the site). Connected to this, are the Individual Object table and the Structure table. The Structure table and the Individual Object table are linked together as well. (I am skipping a few tables in between, but this example should suffice).
It may be the case, that an individual object was found in a structure, however it may also be that case that it was found unassociated with a structure.
So, when I am entering data into a record on the Individual Object table, I need to be able to enter the structure name into a field, which then auto-fills the Sub-Site, and Site field (since the structure is within a sub-site and the sub-site is within a site and these records already have data), but I also need the option to NOT enter a structure name, and enter the sub-site instead, which should then auto-fill the Site field.
I hope that makes sense. This is my first database and my terminology may not be so great...
Any suggestions would be greatly appreciated!