MSAccess Database table Set up using Lookups

Lambel
Lambel used Ask the Experts™
on
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?

Thanks-
Lynn
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
If a site can only ever be in one region, and it will forever be that way, I would just do this:

tblRegions
-------------------------------------
RegionID (PK)
RegionName

tblSites
-------------------------------------
SiteID (PK)
SiteName
RegionID (FK)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
...In matthewspatrick structure, you would only need a lookup in the Sites *Form* (avoid using lookups in tables, see here: The Evils of Lookup Fields in Tables), to select and populate the *RegionID* only.

Author

Commented:
mathhewspatrick: Thanks. That is straight-forward for the table setup. However, it makes the refresh for new sites more complicated for me on the other end. Sites data come from an Oracle db.  I'll need to pull all sites, then run a query against the existing tblSites, and with any resulting new sites, go back around and append them to tblSites. Does that sound appropriate?  
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<No Points wanted>

The only reason you would need a "Many-To-Many" table is if one site could belong to many Regions over a time period

;-)

Jeff

Author

Commented:
boag2000: Thanks for the comments. Interesting article - wish I would have read that sooner.
re 36312283: I'm not sure what you are referring to in your comment about a many-to-many relationship.
I have the tables set up, and I think it is a reliable set up.  But I still wonder if there was a better way to do this.
 I set up three tables:
tblSites: (P)SiteID, SiteName
tblSitesRegions: (P)SiteID, SiteName, Region
tblRegions: Region
On user initiation (command button), a fresh set of Sites is pulled from the Oracle DB, existing records in tblSites are deleted, and the table is updated with the new records.  Then it runs a query on tblSites and tblSitesRegions to find any unmatched (new) sites records, and then those are appended to the tblSitesRegions.  
At that point I'm good to go - the user must update the related region for each site.  

Lynn

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Like matthewspatrick, I still see no reason for the Many to many table.

From what you posted all that is needed is the two tables matthewspatrick posted...

If your experience in databse design tells you that you need it, then roll with it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial