Update A multi-valued field in Access

Question

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 query 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.

Answer 1

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.

Answer 2

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.