Query Multiple Tables
From: Ted Allen (anonymous_at_discussions.microsoft.com)
Date: 06/02/04
- Next message: Michel Walsh: "Re: Query in a query"
- Previous message: Michel Walsh: "Re: I am getting incorrect responses to complicated Count() Query..."
- In reply to: Lisa Reber: "Query Multiple Tables"
- Next in thread: Lisa Reber: "Query Multiple Tables"
- Reply: Lisa Reber: "Query Multiple Tables"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 2 Jun 2004 09:37:14 -0700
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
>.
>
- Next message: Michel Walsh: "Re: Query in a query"
- Previous message: Michel Walsh: "Re: I am getting incorrect responses to complicated Count() Query..."
- In reply to: Lisa Reber: "Query Multiple Tables"
- Next in thread: Lisa Reber: "Query Multiple Tables"
- Reply: Lisa Reber: "Query Multiple Tables"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
