#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] How to update multi-valued lookup field?

## studiosa

Dear Microsoft Gurus,
I am teaching myself Microsoft Access 2007. So, please forgive me if this question is very naive.

I am learning how to use “Update Queries”. I have a database with a field called “specialties needed” . It is a multi-valued field with a lookup. In other words, it has a drop down menu of choices of values and allows choosing more than one value to be chosen. The values are text strings. 

I created a query named “Trying to update null specialties needed field” .

The query does not work as I expected it to.    Ideally, I wanted to find all the rows with empty “specialties needed” field and populate the “specialties needed” field in these rows with two values: “Phy” and “CNA” (so that what I see in that field is *CNA, Phy*) Note that these two values already occur on the list of the possile values for that field. 


I tried several versions of the query.  I keep getting error message: “An Update or Delete query cannot contain a multi-valued lookup field” . 
Am I doing this wrong, or am I trying to do something that is truly impossible to do? If it is impossible to do this, then what does one do if one has a large database with a a multi-valued lookup field and one needs to make some “bulk change” to that field? 

Here is the SQL for the versions of the query that I tried. 

Version 1:  
UPDATE Client_table SET Client_table.[Specialties needed] = "Phy" And "CNA"
WHERE (((Client_table.[Specialties needed].Value) Is Null));

Version 2:
UPDATE Client_table SET Client_table.[Specialties needed] = "Phy" And "CNA"
WHERE (((Client_table.[Specialties needed].Value) Is Null)) OR (((Client_table.[Specialties needed].Value)=""));

Version 3: (Version 3 gives up on the idea of populating that field with two values, and just tries to put in one value. ). 
UPDATE Client_table SET Client_table.[Specialties needed] = "Phy"
WHERE (((Client_table.[Specialties needed].Value) Is Null)) OR (((Client_table.[Specialties needed].Value)=""));

Thank you very much!

Studiosa

----------


## alansidman

Even though multi-valued fields are available beginning with AC 2007, I don't use them.  They are contrary to data normalization.  Here is a short read on data normalization

http://www.deeptraining.com/litwin/d...aseDesign.aspx

I would avoid them as they can cause issues as you are finding out.  Additionally, if you decide to upgrade your Access db to MySQL or SQLServer at some time in the future, you will need to re-do these fields as they will not accept the data in that state.

Having said my piece, here is what MS has to say about using the "new" concept.  You will need to scroll down a bit to find how to update multi-valued fields.

http://office.microsoft.com/en-us/ac...010149297.aspx

----------


## studiosa

Hi Alan,
Thank you so much! This is great. Not only are you very knowledgeable about MS Access, but you are also an excellent teacher!  :Smilie: 

I really appreciate your help. I will avoid multivalued fields. Reading the article, I see that I should redesign the database to avoid multiple-valued fields (make it 1NF)

Unfortunately, the Forum is preventing me from putting a star on your answer because I am not allowed to give the star to the same person twice in a row.   :Mad: 

Studiosa

----------

