filter out records in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a query of contacts that are members of up to five groups. i
eliminated any member that is in group 1 or 2 (by using criteria "not in
(group1, group2)"). my problem is that if the contact is a member of group 3
and 4, he still appears twice in the query. how can i select all contacts
that are of group 3, 4 or 5 except if they are in group 1 or 2 (then they
shouldn't show up at all)and have them show in the query only once?

thanks
 
I think you would have to tell us the structure of your table. Do you have
5 group fields that are yes/no fields? Do you have a drop-down list of
groups? Do you have five records in the table if the person is in all five
groups?

Rick B
 
Assuming you have one table (say, named "Your Table") that looks like this:

Contact Name, Group Number
Joe, 2
John, 1
John, 3
Mary, 3
Mary, 5
Bob, 4

and you want to list Bob (because he's in Group 4), and Mary (because she's
in both Group 3 and 5) but not Joe (because he's not in Group 3, 4, or 5) or
John (because he's in Group 1 even though he's also in Group 3), and you
want to list Bob and Mary only once, you might try a query whose SQL looks
something like this:

SELECT DISTINCT
[Your Table].[Contact Name]
FROM
[Your Table]
WHERE
[Your Table].[Contact Name]
NOT IN
(SELECT
[Self].[Contact Name]
FROM
[Your Table] AS [Self]
WHERE
[Self].[Group Number] IN (1, 2))

This assumes [Contact Name] is unique and non-null.

Like Rick said, the solution will be different if your data is structured a
different way.
 
the query consists of 3 tables connected through several other tables. all
contact information is in one table. each contact invested in a company
(group) and the groups are defined in another table. the "investment table"
connects the "conatct table" with the "company table". contacts might have
invested in several companies. now i want send statements to all contacts,
but i have already sent statements to investors of company 1 and 2, so they
should be totally excluded from the query even if they invested in any other
company.
 
You might try a query whose SQL looks something like this:

SELECT
[contact table].*
FROM
[contact table]
WHERE
[contact table].[contact id]
NOT IN
(SELECT
[investment table].[contact id]
FROM
[investment table]
WHERE
[investment table].[company id] IN (1, 2))

This assumes your table names are as you stated, "contact id" is the primary
key of the "contact table", "contact id" in the "investment table" is not
null and is the primary key of the related contact, and "company id" in the
"investment table" uniquely identifies the company.

This will return all contacts that did not invest in company 1 or 2,
including contacts that did not invest in any company.
 
Back
Top