update a multivalued field access 2007

[Joana Villas-Boas]

I have an old Access 2003 table with logical fields to categorise my clients,
once there where no multivalued fields in that version.

I converted that database to Access 2007 and created a multivalued fields
called categories where I allow multivalues selected. I created a table
called categories where I entered all previous categories I had in Logical
fields.

Now I want to do some update queries that fill in my new multivalued fields
with the categories I had previously with Yes.

It seems easy, but dosen't work! Allways gives an error saying "could not
update because of Validation Rules"!!!

I do a Update query where I put my previous logical field with Yes in the
Criteria and I add the Category.Value Field where I write the name of the
catagory betwwen "" on the update to Field and it simply dosent work.

Can somebody help me?

Thanks

 

[Dale Fye]

Joanna,

My personal opinion: multi-valued fields and "relational database" just
don't mix! Multi-value fields are a direct violation of the First Normal
Form (1NF) of relational databases.

Definition: A relation is said to be in First Normal Form (1NF) if and only
if each attribute of the relation is atomic. More simply, to be in 1NF, each
column must contain only a single value and each row must contain the same
columns.

I believe that MS created this type of field in Access to improve
integration with SharePoint 2007. But there are several issues associated
with doing this. The most obvious of which is that it is difficult to search
or filter on a mult-value field.

I strongly urge you to keep your data structure normallized and avoid
multi-valued fields.