SQL UPDATE that adds, but doesn't replace.

[ClearlyAnIdiot]

Right now, I'm working with an SQL code of

"UPDATE Individuals SET [ShareholderOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"

However, doing so will obviously change the "Shareholder of" field into what the user inputs (Me.CompanyNo). What should I use if I want it to ADD the user input rather than REPLACING the old [shareholderof] value?

 

[pr2-eugin]

Do you want to INSERT INTO the table?

 

[ClearlyAnIdiot]

Do you want to INSERT INTO the table?

Isn't Insert Into for adding a new record entirely? What I'm planning to do is to add to an existing record, much like UPDATE, but without replacing the existing data.

 

[pr2-eugin]

UPDATE as the name suggests UPDATES/Replaces whatever it had in it with a new value.. What exactly you trying to do? By the looks of it, you are trying to Append the New value along with the old value.. Or to put it in simpler words you are trying to store multiple values in the same column, which is against the Rules of Normalization, which states..

Data should be "atomic". That means that data should be stored in its most basic, indivisible form; for example, avoid storing an address as a single data element, with street number, street name, apartment number, city, state and zip code. Each of these are separate pieces of data and should be stored in separate columns.

 

[ClearlyAnIdiot]

Does that mean that just adding into it will be impossible, and that I need to create a separate column in itself for each and every new company added to the person?

 

[pr2-eugin]

It is possible, but not the way you want it to be.. I have no idea how your Table is set up or the table names.. But based on the description.. Every person will have many Companies is that right? I would think you need another table that will have One-To-Many relationship.. You need to create a New company table..

Data should be single valued. That means that you should not have a single field that contains "children's names," for example. If you need to join children's names to their parents' records, do it with relationships between tables, not by entering multiple names into one field in the parents’ record.

 

[CJ_London]

I need to create a separate column in itself for each

It sounds like you need to normalise your data and in this case need three tables along the following lines:

tblPerson
PersonID autonumber PK
PersonName text
...
...

tblCompany
CompanyID autonumber PK
CompanyName text
...
...

tblOwners
PersonID Long FK
CompanyID Long FK

tblOwners is a list of companies 'owned' by each person