Results 1 to 12 of 12
  1. #1
    romeyn is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    4

    Question Shareholder Database Conundrum

    I am designing a database of shareholders for a small corporation. I was all set with three basic tables: Certificates, Shareholders, and Transaction_Details. Then I started data entry. An assumption I made in my design was that each certificate would have only one shareholder associated with it. (That is the corporation's current practice.) But as I started entering historical data, I came to a certificate that had multiple shareholders! This can't be "fixed". It happened and everyone involved is long dead. I have to account for it.



    I'm having trouble coming up with HOW. Each Shareholder has an ID number and each certificate has a number. How do I relate things such that multiple shareholders can be represented as owning a single certificate?

    Examples I find online involve the classic Products/Customers/Orders many-to-many design. But they all assume that each order is made by only one customer.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You would have another table that would hold the PK from both Shareholder and Certificate details.
    It could just be three fields.
    ID Autonumber
    ShareHolderFK Long
    CertificateFK Long
    but you could add any other data associated with the record.

    I have a table I called Links, as that is all it is, but it links Dates to Ships to Crew to Rank

    With that simple file I can generate reports for every date and every ship for that date with all the crew and their rank at that time.

    Click image for larger version. 

Name:	links.png 
Views:	31 
Size:	7.7 KB 
ID:	43164

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Can you upload a screen shot of your Relationship Diagram?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #4
    romeyn is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    4
    Here. And as I look at this I realize I'm going to have some work to do relative to the dates associated with certificates. But that'll be a different problem.

    Click image for larger version. 

Name:	relationship.jpg 
Views:	30 
Size:	66.4 KB 
ID:	43165

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    A few points:-

    1. You should not use spaces in any field name
    2. You should not use any characters like !"£$%^&*()_+ in any field names
    3. I would recommend that you add an object prefix to each table ie tblShareholders, tblTransactions & tblCertificates.
    4. I would add an Autonumber field to your Transactions table and remove the Autonumbers from Certificate_Nr and Sharholder_ID
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Another point: Do not use spaces in ANY object names. (Objects: Tables, Forms, Reports,Queries, Modules)

    Microsoft Access Tables: Primary Key Tips and Techniques

  7. #7
    romeyn is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    4
    Thank you for the comments on style.

    Does anyone have any suggestions about my design problem? I was thinking that maybe I need a new table called "ShareholderGroup" to represent groups of shareholders that may own a single certificate. But then I get hung up on how to populate that table with multiple shareholderIDs. Further, if that's how I'm representing ownership of a certificate, then I'd need a "group of one" for most certificates, as most are owned by only one person. That seems silly, however; but it may be the only way to do this properly...?

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Consider
    Click image for larger version. 

Name:	Junction.png 
Views:	20 
Size:	58.2 KB 
ID:	43193

    Really need some data to test this design. And I'm not sure what some of the fields are for......

    A "FullName" field is not needed because you can concatenate the full name in a column in a query.


    You can have 2 main-form/sub-form forms -
    One form to see/add/edit Certificates owned by a Shareholder
    Main-form --> Shareholder
    Sub-form --> Certificates

    and

    One form to see/add/edit what Shareholders own shares in a Certificate
    Main-form --> Certificates
    Sub-form --> Shareholder

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    each certificate would have only one shareholder associated with it.
    that may be historical, but if a shareholder dies, an executor or executors will become the owners until the shares are either sold or distributed to the beneficiaries. So you will, from a record keeping perspective, have multiple shareholders or the executor might be an organisation (solicitor/bank/whatever). Another scenario is if shares are put into trust - again could have multiple trustees and/or an organisation. So you might want to include an organisation field in the shareholders table.

  10. #10
    romeyn is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    4
    Thank you!

    I clearly need to read up on Foreign Keys. I will do that.

    And "Full Name" is a calculation field derived from First Name and Last Name.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by romeyn View Post
    And "Full Name" is a calculation field derived from First Name and Last Name.
    Just because you can do it, doesn't mean you should.
    Most experienced developers do not use calculated fields in tables .

    What will you do if/when you want "FirstName MiddleName LastName"?
    Or "LastName, FirstName MiddleName"?
    Or "LastName, FirstName"?
    Add 3 more calculated fields??

    I always use Queries/SQL strings for Form/Report record sources so I have more control over the data.


    Some design suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.


    And speaking of Calculated fields in tables, see About calculated table fields

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Ceil Inn tutorial conundrum
    By Lou_Reed in forum Access
    Replies: 3
    Last Post: 04-22-2015, 02:53 PM
  2. Do.. Loop Conundrum
    By DubCap01 in forum Programming
    Replies: 4
    Last Post: 04-18-2015, 01:10 AM
  3. A Datasheet Conundrum
    By Paul H in forum Forms
    Replies: 1
    Last Post: 10-11-2011, 01:46 PM
  4. Conditional Criteria Conundrum
    By nypedestrian in forum Queries
    Replies: 2
    Last Post: 09-01-2011, 12:23 PM
  5. Replies: 4
    Last Post: 11-03-2010, 08:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums