Dynamically combining two or more tables into one
Is it possible to dynamically put data from fields in different tables into a single field in another table?
I have four "country" tables (for sites in England, Northern Ireland, Scotland and Wales). Each of the four country tables has a field containing the site names: "Name England", "Name Northern Ireland" etc. I want my new table, say "UK sites", to contain a field called "Name UK" which holds all the data from the "Name" fields in the other tables. How can I achieve this?
I had thought that in the Relationships graph I would be able to choose "Name UK" and then say "=Name England+Name Northern Ireland" etc, but there is no + symbol and it will not let me link several tables to one other.
I'm doing this because I want the "Name UK" table to be a dynamic Value List for another field called "Staff location". I need the Value List to automatically update when a new site is added to one of the four country tables.
Thanks a million for any advice you can offer.