Update Query with a Multi-Value Field

[tanyalee123]

Yikes! I am trying to import updated records from one DB to my main DB (due to employees entering data onto copies of the database - I now have to merge all of this data into one DB), and I created my update query but am getting the following error: AN UPDATE QUERY CANNOT CONTAIN A MULTI-VALUE FIELD. I do have one multi-value field in my DB. It's a little too late to change that now! I have tens of thousands of records that need to be merged together.

Please tell me there is a way to get around this issue! Thank you. :-)

Tanya

[June7]

Bing: Access update multi-value field

Here is one: http://social.msdn.microsoft.com/For...orum=accessdev

[tanyalee123]

Thank you but I can't write code. I have created a query using the QBE. When I run the query as a select query it works fine and I get the results that I want, but once I change it to an update query, it won't let me as I then get the error.

[June7]

Third post in that referenced thread shows a query solution.

You are adding new records so that is an INSERT, not UPDATE.

[tanyalee123]

So I have looked at the thread a few times, and I am still very confused. I also tried to search and just couldn't find an answer that really helped me (I'm never using mutli-valued fields ever again!!).

QUESTION...is there any way I could change the multi-valued field to something else so I can just get around this issue altogether? What do you do when you need someone to choose more than I item (eg. days of the week, and they can choose more than 1 day of the week...this is my issue, it's a "Best day(s) we can contact you", so they need to be able to choose more than 1 day of the week).

Thank you!!!
Tanya

[June7]

Can't just change the multi-value field to something else.

Have to modify db structure. This means making a related child table from the data saved in the multi-value field. Create a query that expands the multi-value field into multiple records and use that as source for the new table. Then delete the multi-value field.

Only options I know for dynamic parameter for multiple criteria on same field:

1. manually modify the query

2. use VBA, review http://allenbrowne.com/ser-50.html

[tanyalee123]

OK thank you June, as always.

Tanya