You Cannot Add or Change - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

You Cannot Add or Change

Arlette Kent

4 years ago

I'm having an issue with a One to Many database where I'm getting the error code "you can't add or change a record because a related record is required in table" The table they are referring too is the Parent Table. (Relationship type is One-To-Many with Enforce Referential Integrity checked.

The Parent Table is the one "Shareholder" of MANY "Companies" - I'm trying to add the "Shareholder" ID Key to the Companies table and that's when I get that error.

For the life of me I can not figure out what record I'm missing. I have the Parent IDKey (no spelling errors) in the Child Table (Companies) its data type is number, none of the other fields in the table are marked as Required. I've got it working the other way but that means I have the same person listed 12 times for 12 different companies and I thought I would try and get it so that I have that person ONCE and linked to all the different companies they are involved with! Any suggestions you guys can throw at me will be greatly appreciated. I have no idea what I'm doing wrong at this point.

	Adam Schwanz @Reply 4 years ago
Don't you have a many-to-many relationship? Each Shareholder I'm assuming could have multiple companies, and those multiple companies can have multiple shareholders right?

	Adam Schwanz @Reply 4 years ago
I think what you want is Many-to-Many. Unless the company can only have one shareholder.

Arlette Kent OP @Reply

4 years ago

So I have 180 Companies. Those companies have between 1 to 8 shareholders each. I have One Table for the 180 Companies and I have an individual Table for each Shareholder (1-8) so 8 tables. (The reason I have the shareholders divided in this way is because I export data into word for the thousands of Corporate Documents I have. So far I have not figured out a way to get Word to recognize each individual Shareholder unless I do it this way eg: Shrhldr1_FullName / Shrhldr2_FullName. So Shrhldr1 Table has ALL the "First Shareholders" of every company and Shrhldr 2 Table has ALL the "Second Shareholders" of every company etc... To me it reads ONE Shrhldr1 to MANY Companies ONE Shrhldr2 to MANY Companies. I will revisit the many to many side, I created a Junction table (in one of the previous attempts) at one point thinking that's what the issue was but it didn't seem like the right type of relationship and I stopped but maybe I'm just stuck in my head. :) Thank you.

	Scott Axton @Reply 4 years ago
Arlett - Adam pointed you in the right direction. Definitely watch / re-watch the video he suggested. If you get stuck or don't understand something let us know. We're here to help.

Arlette Kent OP @Reply

4 years ago

Right Now I have the Company as the Parent Table with the Shareholder Tables as the Children.  So on the Shareholder side of things I have the same person listed multiple times for each individual company - so I have "joe smith" 8 times in the Shareholder 1 table.  It works but why have Joe eight times when I can have him once to the eight companies.  This happens in the Shareholder 2 table as well but not as many times.  Does this many any sense at all?   These are the tables I have in my databases.

Companies - 1 table
Shareholders 9 tables
Shares Held  11 tables (A thru K)
Directors - 1 table
Accountants - 1 table
Banks Table - 1 table
Tasks - 1 table
Main Contact - 1 table

	Arlette Kent OP @Reply 4 years ago
Thanks Guys! I will watch the Many to Many again!!! :)

	Kevin Robertson @Reply 4 years ago
Also watch Too Many Tables

Adam Schwanz @Reply

4 years ago

Do you care what order the shareholder is? "First Shareholder" or "Second.."? Or just that there's 8? What you need is that many to many relationship, if you care about "first" or "second" just add that as a field in the junction table.

You should have one ShareholderID for JoeSmith, then in the junction table he can be linked to mulitple companies.

Just looking at your table names, I'm thinking you really could combine shareholders, directors, accountants, and main contact into one table (just assuming based on the name, to me those are all people and should store similar information, and main contact to me is just a yes/no checkbox on the record) But that isn't the problem at hand. You could just use a combobox to select what they are, shareholder, directors, etc. But that's another issue (or not)

Arlette Kent OP @Reply

4 years ago

Hey Adam.  I started out with all that info in one table and it was MASSIVE.  Then I moved to One Company many shareholders with everything but the Contact / Accountant / Bank on the shareholders side.

On the Company Side I need to know the company address, phone (etc)  date of incorporation (this has to be entered in as Day of / Month of / year of because of how the docs are written its a legal thing, Corp Access Number, Trade Names, Trade marks. Number of Shareholders (voting & non voting), Corporate Shareholders and Directors.  Then I have a bunch of Yes / No  fields pertaining to what we do each year, how I'm to send the docs.  Some like electronic some do not.  blah blah blah.

On the Shareholder side I need for each individual Shareholder.  Address, phone, occupation Are they a director, Are they an Officers of the company if so what office do they hold  How many shares do they have, What Class of Shares do they have, Are they voting not voting - thats where the A, B C Tables

Arlette Kent OP @Reply

4 years ago

come in to play. As far as the accountants. I have the same accountants for many different companies and that applies as well to the banks. Same bank different companies. So those are drop down pick for a list. The Directors table is a simple one Yes / No with the Shrldr ID Key attached to it. So if its a yes it will generate a Directors Resolutions for that company. 5 shareholders 3 directors means a different set of documents from a company that has 3 Shareholders and 3 Directors or any number of combinations I have.

Arlette Kent OP @Reply

4 years ago

and the order has to do with who holds the most shares. Shareholder 1 will hold the majority of the shares (if not all), is usually an officer and a director but again the identification of Shareholder 1 - Shareholder 2 has also to do with importing the data from Access into word and how word recognizes each individual. I know i've probably over complicated it . But its amazing how much I can create in Word now and how quickly. I can create yearly resolutions for 180 companies in a day. I just think it can be cleaner more efficient. and I love playing around with the program.

	Arlette Kent OP @Reply 4 years ago
HI Kevin - I agree and disagree. Why re enter the Same Accountant and Bank for 50 different companies when I can have a table I get my info from using a combo box which links to the company table then with a query gets exported into word? Why enter Joe Shareholders Information 8 times for 8 different companies when again using a combo box would work???

	Alex Hedley @Reply 4 years ago
So are you using a Junction Table? Table1 ID \| Name 1 \| A 2 \| B Table 2 ID \| Name 1 \| B JunctionTable T1ID \| T2ID 1 \| 1 2 \| 1 Etc

Arlette Kent OP @Reply

4 years ago

Hi Alex! well I'm going to now. i thought it would be an easy flip because of how it worked with the corp table being the parent and the shareholders being the children but i guess not! :) I'm going to just do one for the shareholder 1 table to the company table and see if that works and I don't get the same error. otherwise I will give up and keep duplicating info on the old database. I just wanted it to be smaller, better, faster... you guys are all awesome and I appreciate the input.

Scott Axton @Reply

4 years ago

Arlette you might want to check out the  Groups video extended cut for another example of many to many.
Same thing explained slightly differently.  It helps to have another example sometimes.

This is a good one too because Richard explains / covers Composite Keys.  How to prevent duplicate entries for the same "thing" (share holder and company)

Also - I saw above that you had multiple tables for shares held.  That would be an example of a field that you would add into your JUNCTION table.  In the junction table you can add in fields that are specific to that particular relationship.
Person 1 and Company B.   Shares held.

You mentioned that you wanted to see reverse data such as Company B's Shareholders.
That is real easy too, using the same junction table.  You would make a Subform of the Shareholders.
Then, make a "parent" form of the Company.  Just drop the SubForm into the parent form and you can see (add if you wish) who are Shareholders.

Scott Axton @Reply

4 years ago

Sorry to be adding tons to your plate but I see multiple things in your description that are just hollering out at me.

You might also take a look at the Normalizing Data video, in addition to the Too Many Tables video that Kevin suggested,  to help you distill down your tables.

For instance - Companies are companies - doesn't matter what business they do.  XXX client, Accounting firm, Banks.
People are people no matter where the are or what they do.  It is all in how you relate them.

The Helper Data video would give you lots to think about with regard to attributes you assign to people or things.  Such as ShareType (A,B,C), PositionType (CEO, CFO, Secretary, etc.), Phones (Home, Office, Mobile)  And so on - You get the idea.

I hope I didn't just overwhelm you as I don't know where you are in your DB learning journey.  Baby steps before running.
You don't have to solve all the problems at once - just the next one!  ;-)

Arlette Kent OP @Reply

4 years ago

Hi Scott, Yes I'm revisiting all of the Videos, yes I know I will have to create a junction table that includes all the shareholders. The reason I have so many tables is because of the amount of data I need for each individual shareholder for each company, the reason i have a Director Table / Officer Table is because "Joe" who is a shareholder in 8 companies is a director in four, President in two. Secretary in One and Treasure in another. Plus Joe has Class A voting shares in 4 companies with non-voting shares Class E, & F, he has Non-voting shares of different classes in the other companies. They are simple tables with shrldr IDKeys and Corporation IDKeys with whatever office & Yes/No director which forms part of a query which is exported into word which then propagates the yearly resolutions, dividend resolutions, special resolutions, issuance of new shares, share sales etc. both are created for the shareholders and directors. sometimes its the same people some times its not.

	Arlette Kent OP @Reply 4 years ago
I wish I could show you an example of what I mean and why I did it the way that I did. Corporations are very complex entities with vast variations on their stocks / shares / shareholders / directors / voting & non voting shareholders / officers I could go on and on. Anyways. Thank you for your help. I'll let you know how it goes. :)

	Richard Rost @Reply 4 years ago
If you still can't get it working, let me know. This might make a good video.

Arlette Kent OP @Reply

4 years ago

I'm still getting the exact same error code as before. this time though I'm only trying to join a SINGLE shareholder table to a SINGLE Corporation table. It doesn't matter if I use a junction table or not. not working. I don't get this at all because I can have 1 Accountant to Many Corporations no problem, or 1 Contact to Many Corporations also no problem but I can't have 1 Shareholder to Many Corporations. & yes I've Field Properties. I don't know maybe my Shareholder Table is corrupt. Anyways Thanks everyone for your suggestions. I'm going to just keep using my old database with the repeated shareholders

	Richard Rost @Reply 4 years ago
Try running down the Troubleshooter. Could be a weird bug in there somewhere.

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

Next Unseen

New Feature: Comment Live View