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.

Uneasy

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.

Clino

Nice. In my case I also had to delete first to clear the column out. E.g.: DELETE columnName.Value FROM tableName WHERE columnName.Value > -1;. – Agripinaagrippa

Posted link fails. Here is MS Docs reference support.microsoft.com/en-us/office/… – Revanche

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.

Uneasy

I think you nailed it. Under the hood, the multi-valued field is implemented as you described. But those details are hidden for "convenience"(?). Anyway this kind of confusion is a good reason to avoid multi-valued fields, IMO. The only reason I would use them is if working with SharePoint. – Clino

Answer 3

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.

Struma

yep - multi-value fields are best avoided. – See

Answer 4

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.

Feculent

That query pops up the ""An UPDATE or DELETE query cannot contain a multi-valued field." error message. – Uneasy

Hmm, it seems to work if the column has an existing value but not when the column does not have a value. In other words, you can only update an existing value. – Uneasy

@rsrobbins: can't help you more, I'm sorry. My knowledge ends here with multi-valued fileds because I don't use them... so, I hope my post could have helped you in some way and... well, good luck for your problem :) – Feculent

@Feculent Re: NOTES - it depends what you're doing. I've copied the structure of a table and am investigating how to copy data to this table with multi-valued fields. I inserted all the non-multi-valued fields using an Append query, and figured I could update the mv fields. Now I see I must insert the mv field using an Append query, but I do want to insert data for all records. Of course, I'm using an INNER JOIN between the tables, but I suppose technically, you could use WHERE table1.ID = table2.ID and get the WHERE clause. – Wsan

Answer 5

do not use the .value part

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

Albano

I've already posted this solution and he already told me it's not what he needs :) – Feculent

Answer 6

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

Pernell

Adding a little explanation about where the poster is going wrong with your query would be helpful. – Aftmost

Answer 7

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.

Kamalakamaria