• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1105
  • Last Modified:

FULL OUTER JOIN

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
C_sharp_beaver
Asked:
C_sharp_beaver
  • 6
  • 3
  • 2
  • +2
4 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
0
 
Rey Obrero (Capricorn1)Commented:
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
 
C_sharp_beaverAuthor Commented:
testing...
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
C_sharp_beaverAuthor Commented:
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
 
C_sharp_beaverAuthor Commented:
I agree that FULL OUTER JOIN cannot be created. But I need way around
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
C_sharp_beaverAuthor Commented:
This databasejournal thing is good. Additional problem due to complex query I'm running out AMOUNT OF LINES inside VBA query

Testing...
0
 
8080_DiverCommented:
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
 
C_sharp_beaverAuthor Commented:
Advice about code is perfect but cannot follow due to security restrictions
0
 
8080_DiverCommented:
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
 
8080_DiverCommented:
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
 
Alpesh PatelAssistant ConsultantCommented:
Hi,

Use only Outer Join
0
 
C_sharp_beaverAuthor Commented:
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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 6
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now