Query Multi Value Fields

Is it possible to run a query and show only the values that are not checked
in a multi value lookup field?

I have a list of 80 tasks that HR needs to complete for a new hire. I have
put these tasks in a drop down multi value list. I need to print out a list
of task that are not completed.

Thanks!

The Relational way to find non-existant values is to use a list of the items,
e.g. tlkpTaskType. Then a 2nd table, tblTasks, holds the information about
tasks assigned. Then, to find the tasks not assigned, you would make a query
with an Outer Join between tlkpTaskType and tblTasks, to reveal the tasktypes
that don't exist within the tasks.

The multivalued field for Access was invented to keep up with the SharePoint
world, and probably should NOT be used if you expect to do anything else,
besides work with SharePoint.

So, at the least, to find the 'Not', you will need to have your main list of
tasktypes in a table.

---

Next, make a query to expand all the tasktypes assigned. (You may need to do
per person per data range.)
e.g. SELECT tblMultiValue.ID, tblMultiValue.Data.Value
FROM tblMultiValue;
In this example I have a table with ID and Data, where Data is a multivalued
text field. Using the .Value property of the field forces it to display
every value on a seperate row. (This is where you need to determne per
person, etc.) Save this query. (I called mine qryMV_expanded.)

---

Next create another query with the TaskTypes table and expanded query, using
a right outer join. This is where understanding a little "Set Theory" is
needed to interpret the results.

===

Suppose that you're assigning tasks to people.

Bob - Tasks A, B, D
Sue - Task C
Ed - Tasks F, G, H

Your set of Tasks is A - H, and they're broken up across the 3 people.
Notice that task E has not been assigned. If you only want to know that task
E was not assigned, then you can do a straight outer join and determine that
E is missing.

SELECT tlkpItemType.ItemType
FROM tlkpItemType
LEFT JOIN qryMV_expanded
ON tlkpItemType.ItemType = qryMV_expanded.tblMultiValue.Data.Value
WHERE (((qryMV_expanded.tblMultiValue.Data.Value) Is Null));

Alternatively, suppose your data is the same, but you want to know which
tasks are not assigned per person. In that case, you will need to
specifically run one instance of the query for each person.

Wow, that was long... hope I didn't bore you too badly.

"Debra Lassman" wrote:

Is it possible to run a query and show only the values that are not checked
in a multi value lookup field?

I have a list of 80 tasks that HR needs to complete for a new hire. I have
put these tasks in a drop down multi value list. I need to print out a list
of task that are not completed.

Thanks!