Trouble Searching multivalued field

[Gr0m]

Hi!
I'm kind of a noob to access, but i am still trying to make a database containing the technicians of our companies.


I found a online template i am using, and this has a quick search field.

I have created a multivalued field that contains the technicians skills. (pulldown with checkboxes)

When i try to use the quick search field access returns a error message saying something like:

"The multivalued field '[Category]' can not be used in a WHERE- or HAVING-string."

Freely translated from norwegian....
The field i use for skills is an old category field.... Just renamed and changed a bit..

[June7]

You have just run into one of the reasons multi-value field type is a bad idea. Can't do search on the field.

[Gr0m]

Ok, do you have a better idea to solve my situation?

What i really need is to get a solution, where i easily can find for example a light tech, rigger, or dj from our list of technicians.
I have a long list of technicians, and need to put them in several categories. One technician can do several things, and i need to find them quickly.

Please help?

[orange]

Suggest you build a model of the data subjects involved and how they are related. Seems you would need some of these.

A Technician table to identify uniquely each technician

An "expertise" table

A "category" table

A junction table identifying which technician has which expertise(s)

There are a number of free data models here
http://www.databaseanswers.org/data_...all_models.htm

You should research Normalization to help get your tables designed.

good luck

[June7]

You can either redesign db as suggested by Orange which means a child table for the technician/skills associations or build a query that expands the multi-value field to have each skill on a separate row for each technician. This is what a normalized child table would look like. Then that query could be searched.

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