Avatar of Lambel
 asked on

MSAccess Database table Set up using Lookups

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?

Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

...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.

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 Coachman

<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


Your help has saved me hundreds of hours of internet surfing.

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.  


Jeffrey Coachman

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.