Excluding records

[Bill]

I've been banging my head against the wall trying to figure this out. I
have 3 tables: tblIndividuals, tblTypes and tblIndividualTypes.
tblIndividuals has information on each person that is a member.
tblTypes is a listing of all membership types(ie. Aerobics, Tennis,
Golf). tblIndividualTypes is the link between the two. An individual
can have multiple member types so in tblIndividualTypes the primary key
is made up of a compound key between the PKs of the other two tables.

Now I'm trying to extract information which excludes certain member
types. The problem I run into is the fact that a person can have
multiple member types. For example, one person can have an Aerobics
membership and a Tennis membership. I want to pull all the members
except those that have a Tennis membership. What happens is that for
those that have a tennis membership but also have another type of
membership, their name gets added to the list. It excludes the record
that has the tennis membership info (as it should) but then includes it
for the record that has the aerobics membership. This makes perfect
logical sense, but what I need to accomplish is to have it exclude that
person if they have a tennis membership, regardless of their other
memberships. How can I do this?

TIA,
Bill

 

[Tom Ellison]

Dear Bill:

I've inserted my responses below:

I've been banging my head against the wall trying to figure this out. I
have 3 tables: tblIndividuals, tblTypes and tblIndividualTypes.
tblIndividuals has information on each person that is a member.
tblTypes is a listing of all membership types(ie. Aerobics, Tennis,
Golf). tblIndividualTypes is the link between the two. An individual
can have multiple member types so in tblIndividualTypes the primary key
is made up of a compound key between the PKs of the other two tables.

That sounds good - a typical setup for a many-to-many relationship.

Now I'm trying to extract information which excludes certain member
types. The problem I run into is the fact that a person can have
multiple member types. For example, one person can have an Aerobics
membership and a Tennis membership. I want to pull all the members
except those that have a Tennis membership. What happens is that for
those that have a tennis membership but also have another type of
membership, their name gets added to the list. It excludes the record
that has the tennis membership info (as it should) but then includes it
for the record that has the aerobics membership. This makes perfect
logical sense, but what I need to accomplish is to have it exclude that
person if they have a tennis membership, regardless of their other
memberships. How can I do this?

SELECT IndividualName
FROM tblIndividuals
WHERE IndividualID NOT IN
(SELECT IndividualID FROM tblIndividualTypes
WHERE Type = "Tennis")

The above is a rough idea how to do it. I don't know your specific
field names or whether you have IDs (surrogate keys) for both
Individuals and Types. You you would have to rework it for your
specifics.

There's another way to do it which, if your running a Jet database, is
generally faster:

SELECT I.IndividualName
FROM tblIndividuals I
LEFT JOIN (SELECT IndividualID FROM tblIndividualTypes
WHERE Type = "Tennis") T ON T.IndividualID = I.IndividualID
WHERE T.IndividualID IS NULL

This is the "frustrated outer join" method much favored by Jet SQL
programmers.

If you have any trouble implementing the above, get back with more
details on the column names and how the tables should join (the
primary keys that you put into tblIndividualType).

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Bill]

Ahhh yes...the subquery. I always forget about subqueries. That worked
exactly as I wanted. Thanks a lot.

Bill