<me@scottsolomon.bizwrote
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