AnsweredAssumed Answered

Dynamically combining two or more tables into one

Question asked by trevorlawson on Oct 31, 2008
Latest reply on Dec 15, 2008 by trevorlawson


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.