Query Multiple Tables

[Lisa Reber]

In the course of trying to normalize my database, I have
split my data into multiple tables:
Contacts (name, address, etc.)
Statuses (active member, newsletter receipient, volunteer,
etc.)
ContactStatus (Smith is all of the above, but not a board
member)
The above is a many-to-many relationship, right?

Anyway, I have 4000 names in Contacts and want to send a
bulk mailing to all those within a range of zip codes.
This narrows the list to 3200 names. (I don't want to
have to set up a new status for the mailer, because I can
use the zip code parameters.) Of the 4000 names, 600 have
status = newsletter, and I DON'T want to send the bulk
mailing to those in the zip range who receive the
newsletter, which is 460 names. If I add the ContactStatus
table to the bulk mail query, only those contacts with
some kind of status are included. I can visualize what I'm
after, in terms of sets and subsets from 6th grade? math,
but I can't come up the the right way to write a query
that will extract all Contacts with Status
Not "newsletter". After that all I have to do is apply the
zip code parameters. It seems like it should be simple!

The other thing I've been working on is a new table for
ContactVehicles - for contacts who have registered their
car for the show this year (4 names so far). Then I have
to eliminate them from the bulk mailing too. Should be the
same solution, right?

Thanks in advance - Lisa

 

[Ted Allen]

Hi Lisa,

It sounds like you probably need a subquery for the
Contact criteria. The criteria would look something like:

Not In (SELECT [ContactID] FROM ContactStatus WHERE
[StatusID]=3)

Of course you would have to substitute your actual field
names and the ID number for Newsletter (assuming you used
numbers for the ID's).

Then just add your criteria for the zip code and you
should be done. Note that there is no need to add any
tables to your query other than Contacts.

If you later want to also exclude contacts that have
registered vehicles, you should be able to do that by
just adding the word AND after your subquery and then add
a similar subquery for ContactVehicles.

HTH, Ted Allen

 

[Lisa Reber]

Ted - thanks so much. This worked like a charm. I'm so
grateful to have the newsgroup. ----- (The following seems
to be a rant.) I can understand how it works; what I don't
understand is how to learn when/where/how to use it. Is
this taking VBA code and just plugging it into a criteria?
It seems so simple when it's spelled out for me. I posted
a similar problem and John Vinson showed my how to use
DMax in a query criteria in the same way. OK, I looked up
subquery in the help screen and got "About SQL queries" in
reply. But I never ran across the term subquery before
this. I guess I'm frustrated because I don't know the
right questions to ask. I have 4 reference books - started
with Access97 for Dummies, moved to Access97 Answers!,
then Mastering Access 97. Aso picked up Access97
Programming Unleased which is way over my head. But it can
take all day to try to find the answer. Know of a good SQL
reference website? Anyway, if you read this far, thanks
for letting me blow off steam, and boucoups thanks again
for the prompt and useful answer! - Lisa

 

[Ted Allen]

Hi Lisa,

My pleasure to help, glad it worked for you.

Actually the subquery is just pure sql language, no VBA
code. I agree that Access help isn't always the easiest
to find information in (not to mention the fact that some
things are in Access help and others in VBA help - with
no easy link between the two - drives me nuts I wish you
could search both at once). In the case of subqueries,
and sql in general, it gives the basic structure but not
many examples so it is often easy to overlook how many
ways they can be used.

There is some help on subqueries if you go to Access
Help, then in the contents choose Microsoft Jet SQL
Reference, then Data Manipulation Language and finally
SQL subqueries (at least in my version A2002).

As far as books go, John Viescas has a good listing of
them with his comments on his website at:

http://www.viescas.com/Info/books.htm#Access

He lists a few in particular that focus on using SQL.

When I was first getting familiar with SQL, I found that
it really helped to look at the SQL language of queries
that I was building using the builder, and I then
gradually started modifying them or writing them
completely in SQL view. Once you get used to the basics
of SQL, it becomes much more easy to write subqueries.
In the meantime, when you find that you need a subquery
you can build that query in the builder of a separate
query, switch to SQL view, copy the text, and then paste
it in your main query - then enclose it in parenthesis
and add any necessary prefix such as IN or NOT IN.

This newsgroup is also an extremely good source for
learning since John and many others that are extremely
knowlegdeable post regularly. I'm nowhere near their
class, I'm kind of an intermediate user I guess, and I
find myself learning new things just about every day just
by taking some time to browse through the posts (and
usually reply to a few as well).

Hope that helps.

-Ted Allen

-----Original Message-----
Ted - thanks so much. This worked like a charm. I'm so
grateful to have the newsgroup. ----- (The following seems
to be a rant.) I can understand how it works; what I don't
understand is how to learn when/where/how to use it. Is
this taking VBA code and just plugging it into a criteria?
It seems so simple when it's spelled out for me. I posted
a similar problem and John Vinson showed my how to use
DMax in a query criteria in the same way. OK, I looked up
subquery in the help screen and got "About SQL queries" in
reply. But I never ran across the term subquery before
this. I guess I'm frustrated because I don't know the
right questions to ask. I have 4 reference books - started
with Access97 for Dummies, moved to Access97 Answers!,
then Mastering Access 97. Aso picked up Access97
Programming Unleased which is way over my head. But it can
take all day to try to find the answer. Know of a good SQL
reference website? Anyway, if you read this far, thanks
for letting me blow off steam, and boucoups thanks again
for the prompt and useful answer! - Lisa

-----Original Message-----
Hi Lisa,

It sounds like you probably need a subquery for the
Contact criteria. The criteria would look something like:

Not In (SELECT [ContactID] FROM ContactStatus WHERE
[StatusID]=3)

Of course you would have to substitute your actual field
names and the ID number for Newsletter (assuming you used
numbers for the ID's).

Then just add your criteria for the zip code and you
should be done. Note that there is no need to add any
tables to your query other than Contacts.

If you later want to also exclude contacts that have
registered vehicles, you should be able to do that by
just adding the word AND after your subquery and then add
a similar subquery for ContactVehicles.

HTH, Ted Allen

.

 

[Lisa Reber]

Hi Ted - thanks a bunch! Next week I'll find out how I do.
Your 'Not In' really worked great - Lisa

-----Original Message-----
Hi Lisa,

My pleasure to help, glad it worked for you.

Actually the subquery is just pure sql language, no VBA
code. I agree that Access help isn't always the easiest
to find information in (not to mention the fact that some
things are in Access help and others in VBA help - with
no easy link between the two - drives me nuts I wish you
could search both at once). In the case of subqueries,
and sql in general, it gives the basic structure but not
many examples so it is often easy to overlook how many
ways they can be used.

There is some help on subqueries if you go to Access
Help, then in the contents choose Microsoft Jet SQL
Reference, then Data Manipulation Language and finally
SQL subqueries (at least in my version A2002).

As far as books go, John Viescas has a good listing of
them with his comments on his website at:

http://www.viescas.com/Info/books.htm#Access

He lists a few in particular that focus on using SQL.

When I was first getting familiar with SQL, I found that
it really helped to look at the SQL language of queries
that I was building using the builder, and I then
gradually started modifying them or writing them
completely in SQL view. Once you get used to the basics
of SQL, it becomes much more easy to write subqueries.
In the meantime, when you find that you need a subquery
you can build that query in the builder of a separate
query, switch to SQL view, copy the text, and then paste
it in your main query - then enclose it in parenthesis
and add any necessary prefix such as IN or NOT IN.

This newsgroup is also an extremely good source for
learning since John and many others that are extremely
knowlegdeable post regularly. I'm nowhere near their
class, I'm kind of an intermediate user I guess, and I
find myself learning new things just about every day just
by taking some time to browse through the posts (and
usually reply to a few as well).

Hope that helps.

-Ted Allen

-----Original Message-----
Ted - thanks so much. This worked like a charm. I'm so
grateful to have the newsgroup. ----- (The following seems
to be a rant.) I can understand how it works; what I don't
understand is how to learn when/where/how to use it. Is
this taking VBA code and just plugging it into a criteria?
It seems so simple when it's spelled out for me. I posted
a similar problem and John Vinson showed my how to use
DMax in a query criteria in the same way. OK, I looked up
subquery in the help screen and got "About SQL queries" in
reply. But I never ran across the term subquery before
this. I guess I'm frustrated because I don't know the
right questions to ask. I have 4 reference books - started
with Access97 for Dummies, moved to Access97 Answers!,
then Mastering Access 97. Aso picked up Access97
Programming Unleased which is way over my head. But it can
take all day to try to find the answer. Know of a good SQL
reference website? Anyway, if you read this far, thanks
for letting me blow off steam, and boucoups thanks again
for the prompt and useful answer! - Lisa

-----Original Message-----
Hi Lisa,

It sounds like you probably need a subquery for the
Contact criteria. The criteria would look something like:

Not In (SELECT [ContactID] FROM ContactStatus WHERE
[StatusID]=3)

Of course you would have to substitute your actual field
names and the ID number for Newsletter (assuming you used
numbers for the ID's).

Then just add your criteria for the zip code and you
should be done. Note that there is no need to add any
tables to your query other than Contacts.

If you later want to also exclude contacts that have
registered vehicles, you should be able to do that by
just adding the word AND after your subquery and then add
a similar subquery for ContactVehicles.

HTH, Ted Allen

.

.