Using a combobox value and wildcards to filter a query

[Mystified]

Hi,
I currently have a table that designates tasks, and the people responsible
for each task. Certain tasks have multiple people responsible:

Ex:
Reconciling: Jane Haller, Mary Smith, Kate Pierce
Expense Report: Jane Haller
Filing: Mary Smith, Joan Newell

I've created a form with a combo box that lists the names of each individual
in our department. I would like to be able to choose a name and launch a
query that shows each task that individual is responsible for. I've designed
the query with the criteria: [Forms]![frmTasks]![Combo17]

If I were to select "Jane Haller" from the Combobox, it would only give me
the 2nd row. Is there some kind of wildcard I can insert in the criteria that
will pull the 1st and 2nd row?

Any help would be appreciated. Thanks!

 

[Jeff Boyce]

"How" depends on "what". What does your table/data structure look like?

Do you actually have a field that holds multiple values? (this is not a
good database design)

Which version of MS Access are you using?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mystified]

I'm still designing, so I was seeing if it was possible to do this before I
go forward. I know I should avoid multiple values, but I dont know how else
to show each person that is responsible for a task, without repeating the
task name for each person. If you have any suggestions, Im open to them.

Currently the table is similar to the example below, with names separated by
commas. I am using Access 2003.

Thanks,

"How" depends on "what". What does your table/data structure look like?

Do you actually have a field that holds multiple values? (this is not a
good database design)

Which version of MS Access are you using?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
I currently have a table that designates tasks, and the people responsible
for each task. Certain tasks have multiple people responsible:

Ex:
Reconciling: Jane Haller, Mary Smith, Kate Pierce
Expense Report: Jane Haller
Filing: Mary Smith, Joan Newell

I've created a form with a combo box that lists the names of each
individual
in our department. I would like to be able to choose a name and launch a
query that shows each task that individual is responsible for. I've
designed
the query with the criteria: [Forms]![frmTasks]![Combo17]

If I were to select "Jane Haller" from the Combobox, it would only give me
the 2nd row. Is there some kind of wildcard I can insert in the criteria
that
will pull the 1st and 2nd row?

Any help would be appreciated. Thanks!

 

[Jeff Boyce]

If you are saying that your "domain" includes one person having
"one-to-many" responsibility areas, and each responsibility area having
"one-to-many" persons fulfilling it, you have a "many-to-many" relationship
you need to model.

The way you model it is with three tables:

tblPerson
PersonID
LastName
... (other person-specific info)

tblResponsibilityArea
RAID
RATitle
RADescription
... (other RA-specific info)

trelAssignment
AssignmentID
PersonID
RAID
... (other Assignment-specific info)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm still designing, so I was seeing if it was possible to do this before
I
go forward. I know I should avoid multiple values, but I dont know how
else
to show each person that is responsible for a task, without repeating the
task name for each person. If you have any suggestions, Im open to them.

Currently the table is similar to the example below, with names separated
by
commas. I am using Access 2003.

Thanks,

"How" depends on "what". What does your table/data structure look like?

Do you actually have a field that holds multiple values? (this is not a
good database design)

Which version of MS Access are you using?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
I currently have a table that designates tasks, and the people
responsible
for each task. Certain tasks have multiple people responsible:

Ex:
Reconciling: Jane Haller, Mary Smith, Kate Pierce
Expense Report: Jane Haller
Filing: Mary Smith, Joan Newell

I've created a form with a combo box that lists the names of each
individual
in our department. I would like to be able to choose a name and launch
a
query that shows each task that individual is responsible for. I've
designed
the query with the criteria: [Forms]![frmTasks]![Combo17]

If I were to select "Jane Haller" from the Combobox, it would only give
me
the 2nd row. Is there some kind of wildcard I can insert in the
criteria
that
will pull the 1st and 2nd row?

Any help would be appreciated. Thanks!

 

[John W. Vinson]

I'm still designing, so I was seeing if it was possible to do this before I
go forward. I know I should avoid multiple values, but I dont know how else
to show each person that is responsible for a task, without repeating the
task name for each person. If you have any suggestions, Im open to them.

Use a Many to Many relationship:

People
PersonID
LastName
FirstName
<etc>

Tasks
TaskID
TaskName
<other info about the task itself>

Assignments
PersonID <who's assigned to the task>
TaskID <what task they're assigned to>
<any info about this person/this task, e.g. role, date assigned, completion
status, ...>