Using a combobox value and wildcards to filter a query

  • Thread starter Thread starter Mystified
  • Start date Start date
M

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!
 
"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
 
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,

Jeff Boyce said:
"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 said:
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!
Click to expand...
Click to expand...
 
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


Mystified said:
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,

Jeff Boyce said:
"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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...

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, ...>
 
Back
Top