Access multi-valued fields can be useful but sometimes they can cause problems working with the database. If you eventually would want to move the database to SQL Server you will need to redo certain things so related tables are used instead. This article describes how to solve certain things of this Access-only feature.
The article says: In Office Access 2007 (same with newer versions like Access 2010 and Access 2013), you cannot use an append query that references a table that contains a multivalued field. For example, the following query is not valid:
INSERT INTO NewIssues ( ID, Title, AssignedTo )
SELECT Issues.ID, Issues.Title, Issues.AssignedTo
FROM Issues;
An INSERT INTO query cannot contain a multi-valued field.
Here’s the Issues table:
Depending on what you want to achieve this two step workaround could be an option. First use an append query like this that does not reference any multi-valued field:
INSERT INTO NewIssues ( ID, Title )
SELECT Issues.ID, Issues.Title
FROM Issues;
Resulting in:
Followed by an update query like this that does contain the multi-valued field AssignedTo:
INSERT INTO NewIssues ( AssignedTo.Value )
VALUES ("Lisa")
WHERE NewIssues.Title In
(SELECT NewIssues.Title
FROM Issues INNER JOIN NewIssues
ON Issues.Title = NewIssues.Title);
Final result:
Can this work for updating multiple fields at once? What would I put in Values("Lisa")?
ReplyDeleteI've not tried but it might work. https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/insert-into-statement-microsoft-access-sql?redirectedfrom=MSDN
DeleteReply back how it goes.
Add keyword ".Value" to the multivalued field (Issues.AssignedTo), like this:
ReplyDeleteINSERT INTO NewIssues ( ID, Title, AssignedTo )
SELECT Issues.ID, Issues.Title, Issues.AssignedTo.Value
FROM Issues;
If there are multiple values in the field, then 1 record will be inserted for each value.