I have a database that uses 4 tables that are linked hierarchially: Level 1 to Level 2 to Level 3 to Level 4. I need to combine the key fields of the 4 tables into one table using the same primary key for the related records. I would like the process to work automatically and to work in a fashion that keeps the new table in sync with the source tables (using calculations if possible). If that is not possible, I require a process (merge, import or lookup) that will work with th least amount of difficulty and potential errors. Can someone point me in the right direction?
I have added a mockup of my database showing what I am attempting to do. The 4 tables that are relational cannot be a flat table for the rest of the database as I am using these tables in a hierarchy. Each of these tables have about 50 to a hundred fields and have many other relational tables linked to them. It is only in the case of this one table that I need the data to be entered as if it was a flat file. The question is what is the simplist method to do it?