Link to home
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?

Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
...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.
Avatar of Lambel


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


Avatar of Lambel


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.  


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.