473,287 Members | 1,515 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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.

Aug 26 '06 #1
2 2287
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

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

Aug 26 '06 #2
<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

Aug 26 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
0
by: Robert Wille | last post by:
I have a number of very common queries that the optimizer plans a very inefficient plan for. I am using postgres 7.2.3. I vacuum hourly. I'm wonderingwhat I can do to make the queries faster. Here...
1
by: Robert Wille | last post by:
I have a number of very common queries that the optimizer plans a very inefficient plan for. I am using postgres 7.2.3. I vacuum hourly. I'm wonderingwhat I can do to make the queries faster. Here...
14
by: Darin | last post by:
I have a table that I want to delete specific records from based on data in other tables. I'm more familiar with Access '97, but am now using 2003, but the database is in 2000 format. In '97, I...
6
by: Rory Campbell-Lange | last post by:
The following query on some small datasets takes over a second to run. I'd be grateful for some help in understanding the explain output, and to remake the code. Looks like the sort is using up...
1
by: Ike | last post by:
I have a simple query of joined tables that is failing to give me any rows of data (though, in checking by hand, it certainly should). Essentially, I am trying to return all rows from `ups` that...
24
by: clare at snyder.on.ca | last post by:
I have a SQL query I need to design to select name and email addresses for policies that are due and not renewed in a given time period. The problem is, the database keeps the information for every...
2
by: =?Utf-8?B?Q2hyaXM=?= | last post by:
How can I run this query against a table in my Access database? I don't know hwo to use it in C#. In VB I would use .Recordset = "some sql statement". How do I do this in C#? //I get a vlaue...
5
by: Bob Bridges | last post by:
Start with two tables, parent records in one and child records in the other, a one-to-many relationship. Create a select statement joining the two. Display the query in datasheet mode. When I...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.