Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   TechHelp   Forums   Help   Contact   Merch   Join   Order   Logon  
 
Back to Access Developer Forum    Comments List
Upload Images   @Reply   Bookmark    Link   Email   Next Unseen 
Connect One Field to Multiple Tables
Jacques Swalen 
    
14 months ago
Dear All,

I am creating a CRM system in Access. I have a Company table which I would like to link to a shareholder table. The shareholder table can hold a number of shareholders with a percentage of shares. My problem is, that a shareholder can be a natural person (stored in my contacts table) or a company (stored in my company table). In other words, the shareholder Id can link to either a ContactId. or to a CompanyId. Any tips how this could be done?
John Davy  @Reply  
         
14 months ago
Hi Jacques, Richard did a video and database using Entity which can be either the person or Company. Actually, I built myself a management system for Entities where I keep info on my contacts whether they are people or companies. For example, I determined that the Entity for a family was the Family Name.  The child table would then be the people in that Entity. I create a table to look up the relationship if it exists.  Each person has there own phone, email etc. I will see if I can find that video for you. I could have an entry for the Plumber's Company.(In fact I do)
John
Sami Shamma  @Reply  
             
14 months ago
Jacques

John Davy gave you the best advice. One table for all Entities, and have EntityType as field in the table.
William Dowler  @Reply  
      
14 months ago
When creating structures that adhere to the Normal Form rules the best way to accomplish this is to have one table, maybe called “Party” (the SuperType) that contains all the fields that are common to both Company and Person and a Discriminator field (maybe “PartyType”) which defines which type of table is attached as a Subtype (either Person or Company).
Then create two tables as subtypes (with the SAME primary key as the Supertype for a one-to-one relationship), one called Company and one called Person.  All the fields which are unique to Company go in the Company table and those unique to Person go in the Person table.
There are three ways to manage this data
1. The (most used) is to move all the fields from Person and Company up to the Supertype(Party). Use the Discriminator to determine which set of data you have.
2. Keep all three tables and access Person or Company depending on the Discriminator in Party.
3. (Rarely used). Bring all Party fields down to both Person and Company access Person or Company depending on the Discriminator. (Party can then be a Logical Only entity, i.e. not required in the physical database).
Thomas Gonder  @Reply  
      
14 months ago
In the image below, both Fred and Slate Rock & Gravel Co. are Entities. One is a (fictional) person and the other is a (fictional) Company. A many to many table "links" the two together (I call it a relationship in the ADS).
Thomas Gonder  @Reply  
      
14 months ago

Thomas Gonder  @Reply  
      
14 months ago
Administrator, please delete the above image
Thomas Gonder  @Reply  
      
14 months ago

Jacques Swalen OP  @Reply  
    
14 months ago
Thank you all for your comments! I will give it a try.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.
 

Next Unseen

 
New Feature: Comment Live View
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2026 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 6/16/2026 4:16:51 PM. PLT: 1s