Solved

FULL OUTER JOIN

Posted on 2010-11-16
13
1,068 Views
Last Modified: 2012-05-10
I try to create query inside Excel VBA

Table looks like

Param_1     Param_2   Param_3     Month     Value
 1                  2               3                   1           5
 1                  3               3                   1           6
 1                  3               4                   1           7
 2                  2               3                   2           5
 2                  3               3                   2           6
 1                  3               4                   2           7  

Query looks like

SELECT TBL1.Param_1 as Param1_1, TBL1.Param_2 as Param2_1, TBL1.Param_3 as Param3_1, TBL1.Month as Month_1, TBL2.Month as Month_2, TBL1.Value as Value_1, TBL2.Value as Value_2 FROM Table1 as TBL1 FULL OUTER JOIN Table1 as TBL2 ON  TBL1.Param_1 =  TBL2.Param_1 AND TBL1.Param_2=TBL2.Param_2 AND TBL1.Param_3=TBL2.Param_3 WHERE TBL1.Month = 1 AND TBL2.Month = 2

Method "Open" of Object "Recordset" failed

When I use LEFT or INNER join I get some results but I need FULL OUTER JOIN
I already tried to create copy of table and join them - same result

Please advise
0
Comment
Question by:C_sharp_beaver
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 300 total points
ID: 34149179
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 50 total points
ID: 34149261
you can use a Left, Right Joins and Union the queries to get the results like what the Full Outer joins will return

something like this

SELECT *
FROM   employee
       LEFT JOIN department
          ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM   employee
       RIGHT JOIN department
          ON employee.DepartmentID = department.DepartmentID;



0
 

Author Comment

by:C_sharp_beaver
ID: 34149649
testing...
0
 

Author Comment

by:C_sharp_beaver
ID: 34149751
UNION does not help

as you can see I have more complex ON restrictions. By some reason does not help. Does not collaps but amount of records like INNER JOIN
0
 

Author Comment

by:C_sharp_beaver
ID: 34149774
I agree that FULL OUTER JOIN cannot be created. But I need way around
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 300 total points
ID: 34149894
This still uses the UNION idea, but there is one extra join:

http://www.databasejournal.com/features/msaccess/article.php/3516561/Implementing-the-Equivalent-of-a-FULL-OUTER-JOIN-in-Microsoft-Access.htm

I've looked at a dozen posts, and they all point to the UNION work around.  Not saying there is not another way, just not seeing it yet.

mx
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:C_sharp_beaver
ID: 34150007
This databasejournal thing is good. Additional problem due to complex query I'm running out AMOUNT OF LINES inside VBA query

Testing...
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34150093
C_sharp_beaver,

It would help if you would post the SQL that you are testing.  (Also, please put it in the Code section and attach it to the response. ;-)
0
 

Author Comment

by:C_sharp_beaver
ID: 34150125
Advice about code is perfect but cannot follow due to security restrictions
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 150 total points
ID: 34150139
C_sharp_beaver,

Was your test SQL something like the attached?
SELECT TBL1.Param_1           AS Param1_1

     , TBL1.Param_2           AS Param2_1

     , TBL1.Param_3           AS Param3_1

     , TBL1.Month             AS Month_1

     , TBL2.Month             AS Month_2

     , TBL1.Value             AS Value_1

     , TBL2.Value             AS Value_2

FROM   Table1                 AS TBL1

LEFT OUTER JOIN Table1 AS TBL2

       ON      TBL1.Param_1   =  TBL2.Param_1

              AND TBL1.Param_2=TBL2.Param_2

              AND TBL1.Param_3=TBL2.Param_3

WHERE  TBL1.Month             = 1

       AND TBL2.Month         = 2

UNION

SELECT TBL1.Param_1           AS Param1_1

     , TBL1.Param_2           AS Param2_1

     , TBL1.Param_3           AS Param3_1

     , TBL1.Month             AS Month_1

     , TBL2.Month             AS Month_2

     , TBL1.Value             AS Value_1

     , TBL2.Value             AS Value_2

FROM   Table1                 AS TBL2

LEFT OUTER JOIN Table1 AS TBL1

       ON      TBL1.Param_1   =  TBL2.Param_1

              AND TBL1.Param_2=TBL2.Param_2

              AND TBL1.Param_3=TBL2.Param_3

WHERE  TBL1.Month             = 1

       AND TBL2.Month         = 2

AND TBL1.Param1 IS NULL

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34150169
C-sharp_beaver,

Advice about code is perfect but cannot follow due to security restrictions

You already posted some code . . . if that code is what your code is like, then posting a similarly obfuscated clip of SQL indicated your test SQL would probably suffice.  Please note that, since we have no idea what you are writing and testing, we have no idea whether your test SQL is anywhere near correct. ;-)
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34153121
Hi,

Use only Outer Join
0
 

Author Closing Comment

by:C_sharp_beaver
ID: 34156244
We trying to work around limitation of specific product.

As soon as application progress let's say to SQL Server our life will become easy

Life is perfect in perfect world (c)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now