473,569 Members | 2,532 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Outer Join possible in Access 2002?

From what I understand, an Inner Join narrow the rows selected to the table
with the least results... and an Outer Join does the opposite...

SELECT qryTxToQ1.Q1Tot al, qryTxToQ2.Q2Tot al, qryTxToQ3.Q3Tot al,
qryTxToQ4.Q4Tot al
FROM qryTxToQ4 OUTER JOIN (qryTxToQ3 OUTER JOIN (qryTxToQ1 OUTER JOIN
qryTxToQ2 ON qryTxToQ1.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON
qryTxToQ3.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON qryTxToQ4.TxAcc t_ID =
qryTxToQ3.TxAcc t_ID;

Is my syntax wrong? Or am I trying to do something that Access 2002 will
not allow?
Nov 12 '05 #1
3 7534
Outer joins are more involved in that there are two types (Left Outer and
Right Outer).

Yes your syntax is wrong.

I can't tell what you are trying to do.

Try building your query in the Access query builder first and looking at
the SQL that it creates. But you need to look at Help for 'Join Types',
first.
Double clicking the join line in the query grid will show the Join
options, but there are restrictions on what will work with what when you
have multiple joins.

Regards

Peter Russell


deko previously wrote:
From what I understand, an Inner Join narrow the rows selected to the
table
with the least results... and an Outer Join does the opposite...

SELECT qryTxToQ1.Q1Tot al, qryTxToQ2.Q2Tot al, qryTxToQ3.Q3Tot al,
qryTxToQ4.Q4Tot al
FROM qryTxToQ4 OUTER JOIN (qryTxToQ3 OUTER JOIN (qryTxToQ1 OUTER JOIN
qryTxToQ2 ON qryTxToQ1.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON
qryTxToQ3.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON qryTxToQ4.TxAcc t_ID =
qryTxToQ3.TxAcc t_ID;

Is my syntax wrong? Or am I trying to do something that Access 2002
will
not allow?


Nov 12 '05 #2
pi********@hotm ail.com (Pieter Linden) wrote in
news:bf******** *************** ***@posting.goo gle.com:
Access won't do the FULL OUTER JOIN that (I think) exists in Oracle.
You can do LEFT/RIGHT JOINs though. If you want to see the Access
syntax, create a query in the QBE grid with two related tables.
Right-Click on the join line and select either option 2 or 3 (both
outer joins). But those are LEFT or RIGHT, not FULL. I think to get
what you want, you may need to create the LEFT and RIGHT joins and
then UNION them together...


You'll end up with dupes that way, unless you filter them:

SELECT tblA.AField1, tblB.BField1 FROM tblA LEFT JOIN tblB ON tblA.ID = tblB.ID
UNION
SELECT tblA.AField1, tblB.BField1 FROM tblA RIGHTJOIN tblB ON tblB.ID = tblA.ID
WHERE tblA.ID IS NULL

--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and
you can travel to places you never heard of and still understand some people." -- Alex K. Angelopoulos
Nov 12 '05 #3
Thanks everyone for the responses - much appreciated!

I've discovered that full outer joins are not supported in an access .mdb
(see:
http://office.microsoft.com/assistan...EC010553071033)

However, I was successful in creating the following, which solves my
problem:

SELECT tblTxAcct.TxAcc t_ID, qryTxToQ1.Q1Tot al, qryTxToQ2.Q2Tot al,
qryTxToQ3.Q3Tot al, qryTxToQ4.Q4Tot al, qryTxToYr.YrTot al,
tblTxAcct.TxAcc tName
FROM ((((tblTxAcct LEFT JOIN qryTxToQ1 ON tblTxAcct.TxAcc t_ID =
qryTxToQ1.TxAcc t_ID) LEFT JOIN qryTxToQ2 ON tblTxAcct.TxAcc t_ID =
qryTxToQ2.TxAcc t_ID) LEFT JOIN qryTxToQ3 ON tblTxAcct.TxAcc t_ID =
qryTxToQ3.TxAcc t_ID) LEFT JOIN qryTxToQ4 ON tblTxAcct.TxAcc t_ID =
qryTxToQ4.TxAcc t_ID) LEFT JOIN qryTxToYr ON tblTxAcct.TxAcc t_ID =
qryTxToYr.TxAcc t_ID;

I think I can use something like Nz(Q1Total,0) in the data sheet to populate
blank cells with zeros... but have not yet tried this...


"Pieter Linden" <pi********@hot mail.com> wrote in message
news:bf******** *************** ***@posting.goo gle.com...
"deko" <dj****@hotmail .com> wrote in message

news:<WC******* *********@newss vr29.news.prodi gy.com>...
From what I understand, an Inner Join narrow the rows selected to the table with the least results... and an Outer Join does the opposite...

SELECT qryTxToQ1.Q1Tot al, qryTxToQ2.Q2Tot al, qryTxToQ3.Q3Tot al,
qryTxToQ4.Q4Tot al
FROM qryTxToQ4 OUTER JOIN (qryTxToQ3 OUTER JOIN (qryTxToQ1 OUTER JOIN
qryTxToQ2 ON qryTxToQ1.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON
qryTxToQ3.TxAcc t_ID = qryTxToQ2.TxAcc t_ID) ON qryTxToQ4.TxAcc t_ID =
qryTxToQ3.TxAcc t_ID;

Is my syntax wrong? Or am I trying to do something that Access 2002 will not allow?


Access won't do the FULL OUTER JOIN that (I think) exists in Oracle.
You can do LEFT/RIGHT JOINs though. If you want to see the Access
syntax, create a query in the QBE grid with two related tables.
Right-Click on the join line and select either option 2 or 3 (both
outer joins). But those are LEFT or RIGHT, not FULL. I think to get
what you want, you may need to create the LEFT and RIGHT joins and
then UNION them together...

Okay, that's enough "thinking" for one day...

Nov 12 '05 #4

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

Similar topics

0
482
by: Preston Landers | last post by:
Hello all. I am trying to write a query that "just" switches some data around so it is shown in a slightly different format. I am already able to do what I want in Oracle 8i, but I am having trouble making it work in SQL Server 2000. I am not a database newbie, but I can't seem to figure this one out so I am turning to the newsgroup. I...
8
4964
by: Matt | last post by:
Hello I have to tables ar and arb, ar holds articles and a swedish description, arb holds descriptions in other languages. I want to retreive all articles that match a criteria from ar and also display their corresponding entries in arb, but if there is NO entry in arb I still want it to show up as NULL or something, so that I can get...
5
15170
by: Harry Broomhall | last post by:
I wonder if anybody could give me a few pointers on a problem I face. I need to do an UPDATE on table A, from an effective left outer join on A and another table B. (This is trying to perform a number translation, where the items that need it are rare.) The following points *I think* are relevant: 1) The FROM clause in UPDATE should...
4
8847
by: Anthony Robinson | last post by:
I was actually just wondering if someone could possibly take a look and tell me what I may be doing wrong in this query? I keep getting ambiguous column errors and have no idea why...? Thanks in advance!!! SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,...
3
23080
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but he doesn't know how to do that, or even if he can, and i don't have to time to learn DB2 from scratch right now. The following SQL Query is a...
1
2866
by: Bob Alston | last post by:
I am trying to use an access sql statement in a module that does a left outer join on one table - joined to a table that has a selection criteria. The result is an inner join. If I do this in two pieces using queries, it works correctly. If I remove the criteria, it works correctly but of course gives me more matches than I want. ...
3
17826
by: Doug | last post by:
Hi, I'm more familiar with MSSQL than Access syntax and have run into a problem with correctly putting ( )'s around the joins in a 3 table query. I want to INNER JOIN lenders and accounts and LEFT OUTER JOIN that result with prospects. (I want to receive all the results of the inner join and any pertinent info from table 3 that is...
5
3272
by: Eitan M | last post by:
Hello, How can I do outer join in ms-access database ? Thanks :)
1
1370
by: Eitan M | last post by:
Hello, I want to do left outer join in access 2002. 1) Isn't any way to do without the words "left outer join", like : table1.column1 = table2.column2 (+) that was the simple Oracle syntax, and is there any access syntax. 2) I want to do nested outer join - how ? Need an example, please. I have tried something like :
0
7628
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8145
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7694
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5519
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5245
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3679
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3667
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2128
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
970
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.