Update Multi-Valued Field in Access

Question

I created a lookup table in Access to provide possible values ​​for the column. Now I need to update this column with the data that it had before I reworked the column. I can not find SQL Query that will work. I keep getting the error "UPDATE or DELETE query cannot contain a multi-valued field." My research has shown that I just need to set the column value, but this always updates 0 records:

UPDATE [table_name] SET [column_name].Value = 55 WHERE [table_name].ID = 16; 

I know this query will work if I change it to update a text column, so this is definitely a problem only with this column.

Answer 1

If you are adding a value to a multi-valued field, use the add request.

 INSERT INTO table_name( [column_name].Value ) VALUES (55) WHERE ID = 16; 

If you want to change a single value that exists in your multi-valued field, use the UPDATE statement. For example, to change the value from 55 to 56 ...

 UPDATE [table_name] SET [column_name].Value = 56 WHERE [column_name].Value = 55 And ID = 16; 

For more information, see Using Multivalued Fields in Queries .

Answer 2

I get it! This, of course, is contradictory! You must use the INSERT statement to update.

 -- Update a record with a multi-valued field that has no value INSERT INTO [table_name] ( [[column_name].[Value] ) VALUES(55) WHERE [table_name].ID = 16; 

This confused me because I was expecting an UPDATE statement. I think it actually inserts a record into a hidden table, which is used to bind multiple values ​​to this column.

Answer 3

I work with Sharepoint, I created tables as multi-valued fields, encountered an error with my INSERT INTO , returned to Sharepoint to go to fields other than multiple values, but this did not fix It.

I recreated the table without using multi-valued fields, while INSERT INTO worked fine.

Answer 4

I must say that I did not understand your problem very well, but I saw something strange in your request. Try the following:

 UPDATE [table_name] SET [column_name]= 55 WHERE [table_name].ID = 16; 

UPDATE :
Check out this link: it has an example

 UPDATE Issues SET Issues.AssignedTo.Value = 10 WHERE (((Issues.AssignedTo.Value)=6) AND ((Issues.ID)=8)); 

NOTES

You should always include WHERE, which identifies only which you want to update. Otherwise, you will update records that you do not intend to change. Refresh a query that does not contain a WHERE clause modifies each row in a table. You can specify one value for the change.

Answer 5

do not use the .value part

 UPDATE [table_name] SET [column_name] = 55 WHERE [table_name].ID = 16; 

Answer 6

 INSERT INTO Quals (cTypes.[value]) SELECT Quals_ContractTypes.ContractType FROM Quals_ContractTypes WHERE (Quals.ID = Quals_ContractTypes.ID_Quals); 

Answer 7

The Multi-Valued field refers to Access databases with column tables that allow you to select multiple values, such as a Combo Checkbox list.

THOSE are the only access types that SQL cannot work with. I tested all of Access's search capabilities, including hard-coded values ​​and lookup tables. They work fine, but if you have a column with Allow Multiple select options, you're out of luck. Even using INSERT INTO, as indicated below, will not work, since you will get a similar but different error about INSERTing in multi-valued fields.

As already mentioned, it is best to avoid using such tables outside Access and refer to the table specifically for your external needs. Then write a macro / vba script to update real tables with data from the "auxiliary" table.