Running a Query to Obtain One of Multiple Values in a Field

[lah5240]

I have a contact database which includes a field for category to
organize the contacts into correct groups for an email list.

The problem I am having is that when I try to sort or query to pull up
a specific category is that the people who belong to more than one
category (for example in the field it would read BZ, DW or something
along those lines) and when I filter or query it will not include any
of the people that have these multiple values, it only returns people
with just that one cateogry grouping.

My question is whether or not there is a formula or notation I can use
so that it will pull up any field that contains BZ or DW, etc. so I
can get a complete return of everyone belonging to that group, even if
they belong to another one as well.

I've been scratching my head for days on this one so any ideas or help
would be much appreciated! Thanks!

 

[Rick Brandt]

I have a contact database which includes a field for category to
organize the contacts into correct groups for an email list.

The problem I am having is that when I try to sort or query to pull up
a specific category is that the people who belong to more than one
category (for example in the field it would read BZ, DW or something
along those lines) and when I filter or query it will not include any
of the people that have these multiple values, it only returns people
with just that one cateogry grouping.

My question is whether or not there is a formula or notation I can use
so that it will pull up any field that contains BZ or DW, etc. so I
can get a complete return of everyone belonging to that group, even if
they belong to another one as well.

I've been scratching my head for days on this one so any ideas or help
would be much appreciated! Thanks!

One should never store more than one value in a field. You are experiencing one
of the reasons for that rule.

You should have another related table (one to many relationship).

 

[Lovelee7285]

One should never store more than one value in a field. You are experiencing one
of the reasons for that rule.

You should have another related table (one to many relationship).

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

So if I create a related table how would be the best way to format it
to get the results that I need....I'm a little bit new to
access...fairly familiar with how it works, just not familiar with
actually utilizing its capabilities....thanks so much!

 

[Rick Brandt]

So if I create a related table how would be the best way to format it
to get the results that I need....I'm a little bit new to
access...fairly familiar with how it works, just not familiar with
actually utilizing its capabilities....thanks so much!

Usual method is to use a form with a subform. Then each category entry is a
separate record in the subform. The subform would be linked to the main form
via the contact primary key field (which would be a foreign key in the
categories tale).

 

[Michel Walsh]

If your data is like:

PeopleID, GroupID ' fields
Jim AA
Jim Bz
Mary O
Mary Z ' data


SELECT PeopleID FROM mytable WHERE GroupID IN("Bz", "Dw")


will return Jim.


SELECT PeopleID
FROM myTable
WHERE PeopleID NOT IN( SELECT PeopleID FROM mytable WHERE GroupID IN("Bz",
"Dw") )

would select Mary (Jim would be excluded, since it has a groupID in the
unwanted list).



Now, if the list is variable, or long, you may decide to make a table (it
will be far easier to add/remove groups in a table than to edit your SQL
code):


UnwantedGroups ' table name
GroupID ' field
Bz
Dw ' data


And an easier (and faster at runtime, well, hopefully faster) query, instead
of the last one, would be:



SELECT a.PersonID
FROM myTable AS a LEFT JOIN unwantedGroups AS b
ON a.groupID=b.groupID
GROUP BY a.PersonID
HAVING COUNT(b.groupID) = 0



Hoping it may help,
Vanderghast, Access MVP