Update A multi-valued field in Access

I have created a lookup table in Access to provide the possible values for a column. Now I need to update this column with the data it had before I converted the column. I am unable to figure out a SQL Query that will work. I keep getting the error "An UPDATE or DELETE qu开发者_高级运维ery cannot contain a multi-valued field." My research has suggested that I just need to set the value of the column 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 it is definitely a problem with just this column.

If you're adding a value to your multi-valued field, use an append query.

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

If you want to change one particular value which exists in your multi-valued field, use an UPDATE statement. For example, to change the 55 to 56 ...

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

See Using multivalued fields in queries for more information.

I have figured this out! It certainly was counter-intuitive! You have to use an INSERT statement to do the 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 that is used to associate multiple values with this column.

I am working with Sharepoint, I created the tables as multi-value fields, ran into the error with my INSERT INTO statement, went back to Sharepoint to change to non-multi-value fields, but that didn't fix it.

Recreated the table without using multi-value fields, and the INSERT INTO worked just fine.

do not use the .value part

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

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

I gotta say I didn't understand very well your problem but I saw something strange in your query. Try this:

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

UPDATE:
Look at 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 a WHERE clause that identifies only the records that you want to update. Otherwise, you will update records that you did not intend to change. An Update query that does not contain a WHERE clause changes every row in the table. You can specify one value to change.

The Multi-Valued field refers to Access databases that have tables with columns, that allow you to select multiple values, like a Combo Checkbox list.

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

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