I have 2 tables: tblRegions, tblSites. tblRegions has one field: Regions. tblSites has two fields SiteID and SiteName.
Each Site is a subset of one Region. So Regions has a 1:n relationship with Sites. I want to create a table that assigns the relationships between regions and sites. I've called it tblRegionsSites. It has three fields: SitesID, SiteName, Region. I want to use a lookup to allow the user to create a record by selecting a SiteID, which would populate the SiteID AND the SiteName fields, then they would us a combobox to select the Region. Does this sound like a reasonable setup? I'm having trouble figuring out how to get the SitesName field to populate from the selection of a SiteID. Can someone explain how to set this up, or, if it is a bad design, what a better approach would be?