Query Joining Four Tables, Return Rows from One Table Only

[me]

I have written a query that joins four or five tables. One table has
30,000 rows. Another table has only 200. I want to only return the
200 or so rows in the smaller table and columns from the other tables
where data in the surrogate keys match, without returning the other
data. It is an outer join, (for four tables, which one is the left and
which one is the right?) and how do you define it in design mode in
Msft Access Queries, as opposed to SQL code, to get it to work right?

email to Sl******@aol.com any suggestions,

I am willing to recompense a responder with a gift certificate of their
choice for accuracy and a quick response.

[Larry Linson]

Visit http://www.mvps.org/access/netiquette.htm for good suggestions on
effective use of newsgroups. Hint: you'll find that asking for an e-mail
response is a good way to get no response at all. I don't know if it says
so, but offering compensation to people who spend uncounted hours helping
others for free is considered insulting and may assure that those
best-qualified to help in the newsgroup will let whoever is here only to try
to promote their business handle it.

I have, however, responded to your question, separately, ONLY in the
newsgroup. You ask here, we answer here -- and others can benefit from the
exchange.
Larry Linson
Microsoft Access MVP

<[e-mail] in message
news:11**********************@74g2000cwt.googlegro ups.com...

>I have written a query that joins four or five tables. One table has
30,000 rows. Another table has only 200. I want to only return the
200 or so rows in the smaller table and columns from the other tables
where data in the surrogate keys match, without returning the other
data. It is an outer join, (for four tables, which one is the left and
which one is the right?) and how do you define it in design mode in
Msft Access Queries, as opposed to SQL code, to get it to work right?

email to Sl******@aol.com any suggestions,

I am willing to recompense a responder with a gift certificate of their
choice for accuracy and a quick response.

[Larry Linson]

<[e-mail]

I have written a query that joins four or
five tables. One table has 30,000 rows.
Another table has only 200. I want to only
return the 200 or so rows in the smaller
table and columns from the other tables
where data in the surrogate keys match,
without returning the other data. It is an
outer join, (for four tables, which one is
the left and which one is the right?) and how
do you define it in design mode in Msft
Access Queries, as opposed to SQL code,
to get it to work right?

The implication is that you have written SQL from scratch, when you could
have invested a few minutes in learning Query Builder. Sad.

Open the Query Builder. Add each of the Tables of interest. It will be
convenient, but not necessary, to add the one from which you want all the
rows as the first one. Click on the Field on which you want to Join, and
drag to the corresponding Field in the Table you want to Join. Repeat for
each related Table. Now, click on the Join line to highlight it,
right-click the highlighted Join line, and choose Join properties. This will
open a dialog box, and (perhaps to your surprise) you'll find that it
doesn't ask you which is Right and which is Left but gives you simple
choices... so you can choose from which Table you want "all records" and
from which you want "only those that match." Again, repeat for each related
table. Drag down to the grid those Fields you want to retrieve.

The rightmost item in the Menu is "Help." That's where to go for assistance
if your Query turns out to be un-updateable; chances are good that you can
fix it with just the information from Help.

Larry Linson
Microsoft Access MVP