MS Access Full Outer Join using 2 fields?
I would suggest a union of two left joins
to give the same result as full outer, and finally with a touch of sorting to yield the desired ordering:
select c.* from
(
select a.id, a.value, a.date, b.fcst, b.outdate
from a left join b on a.id = b.id and a.date = b.outdate
union
select b.id, a.value, a.date, b.fcst, b.outdate
from b left join a on a.id = b.id and a.date = b.outdate
) c
order by c.id, nz(c.date, c.outdate)
Full Outer Joins in MS Access
Access (unfortunately) does not support OUTER JOIN. What it does is INNER, LEFT and RIGHT JOINS.
- INNER JOIN - Gives you only the information common to two tables.
- RIGHT JOIN - Gives all information that are common to the two table and information that are not matched form the table that is on the RIGHT to the JOIN statement.
- LEFT JOIN - Gives all information that are common to the two table and information that are not matched form the table that is on the LEFT to the JOIN statement.
- OUTER JOIN - is the UNION of RIGHT JOIN and LEFT JOIN.
So as expected, you just have to perform two Queries that perform a JOIN in both directions and then marry them using a UNION. This is a long process, but is the only way !
SELECT * FROM
(SELECT table1.FieldName1, table2.FieldName2
FROM table1 RIGHT JOIN table2 ON table1.ID = table2.ID
UNION
SELECT table1.FieldName1, table2.FieldName2
FROM table1 LEFT JOIN table2 ON table1.ID = table2.ID)
Full Join on MS Access
Your ultimate goal is to emulate a FULL OUTER JOIN, but your first hurdle is that Access' database engine complains about your LEFT JOIN attempt. You need to first create a workable JOIN, and I can't spot what's wrong with the sample you provided.
Does Access accept this simplified version?
SELECT *
FROM
(tbl_Vendors AS vend
INNER JOIN tbl_Inventory AS inv
ON vend.vid = inv.VendorID)
LEFT JOIN tbl_ItemHistory AS hist
ON inv.ItemNum = hist.ItemNum;
For the moment, we're not concerned about the field list or ORDER BY ... simply whether that query works without error and returns the correct rows.
If it does work, see whether this RIGHT JOIN returns the remaining rows you need.
SELECT *
FROM
(tbl_Vendors AS vend
INNER JOIN tbl_Inventory AS inv
ON vend.vid = inv.VendorID)
RIGHT JOIN tbl_ItemHistory AS hist
ON inv.ItemNum = hist.ItemNum
WHERE inv.ItemNum Is Null;
You may need to change the WHERE clause; that was untested air code. But if that also works, combine the 2 queries into one:
SELECT *
FROM
(tbl_Vendors AS vend
INNER JOIN tbl_Inventory AS inv
ON vend.vid = inv.VendorID)
LEFT JOIN tbl_ItemHistory AS hist
ON inv.ItemNum = hist.ItemNum
UNION ALL
SELECT *
FROM
(tbl_Vendors AS vend
INNER JOIN tbl_Inventory AS inv
ON vend.vid = inv.VendorID)
RIGHT JOIN tbl_ItemHistory AS hist
ON inv.ItemNum = hist.ItemNum
WHERE inv.ItemNum Is Null;
Can I use FULL OUTER JOIN in access VBA?
It depends which database you are trying to connect to. If you are connecting to a database which supports FULL OUTER JOIN
, then you could use FULL OUTER JOIN
from VBA.
Since you're using CurrentDb.OpenRecordset
, you are querying with the Access / Jet database engine, and FULL OUTER JOIN
is not supported.
If you are targeting another RDBMS which does support FULL OUTER JOIN
, then you could create a pass-through query with FULL OUTER JOIN
and call OpenRecordset
on that query.
What is the MS Access SQL equivalent of FULL OUTER JOIN with a.key IS NULL and b.key IS NULL
Use:
select . . .
from a
where not exists (select 1 from b where b.key = a.key)
union all
select . . .
from b
where not exists (select 1 from a where a.key = b.key);
The . . .
is for the columns that you want.
You could use *
if you used:
select a.*, b.*
from a left join
b
on 1 = 0
where not exists (select 1 from b where b.key = a.key)
union all
select a.*, b.*
from b left join
a
on 1 = 0
where not exists (select 1 from a where a.key = b.key);
Access FULL JOIN for 7 tables
Since FULL OUTER JOIN
is not available in MS Access and requires a UNION ALL
of LEFT JOIN
and RIGHT JOIN
, consider breaking apart the process into several queries. This avoids very nested subquery derived tables:
Query 1 (first two tables)
SELECT a.ClientID, a.PRICE_A, b.PRICE_B
FROM ClientPriceA a LEFT JOIN ClientPriceB b
ON a.CLIENTID = b.CLIENTID
WHERE a.ID IS NOT NULL;
UNION ALL
SELECT b.ClientID, a.PRICE_A, b.PRICE_B
FROM ClientPriceA a RIGHT JOIN ClientPriceB b
ON a.CLIENTID = b.CLIENTID
WHERE b.ID IS NOT NULL;
Query 2 (first two tables query + third table) -- GENERATES POSTED DESIRED OUTPUT
SELECT m.*
FROM
(SELECT f.ClientID, f.PRICE_A, f.PRICE_B, c.PRICE_C
FROM Query1 f
LEFT JOIN ClientPriceC c ON f.ClientiD = c.ClientID
WHERE f.ClientID IS NOT NULL
UNION ALL
SELECT c.ClientID, f.PRICE_A, f.PRICE_B, c.PRICE_C
FROM Query1 f
RIGHT JOIN ClientPriceC c ON f.ClientiD = c.ClientID
WHERE c.ClientID IS NOT NULL
) AS m
ORDER BY m.ClientID;
Then repeat process for all others:
Query 3 (first three tables query + fourth table)
...
Query 4 (first four tables query + fifth table)
...
Query 5 (first five tables query + sixth table)
...
Query 6 (first six tables query + seventh table) --FINAL RESULT
...
NOTE: You may run into an MS Access UNION
limit or query too complex and so consider outputting queries into temp tables with make-table calls: SELECT * INTO FROM (<query>)
.
Related Topics
SQL Server 2008 Cross Tab Query
How to Set a Size Limit for an "Int" Datatype in Postgresql 9.5
MySQL - Change Date String to Date Type in Place
Update Values in Struct Arrays in Bigquery
Regex Remove All Occurrences of Multiple Characters in a String
Left Outer Join and an Additional Where Clause
What Is the Effect of Omitting Size in Nvarchar Declaration
Issues with SQL Comparison and Null Values
Foreign Keys on Table from Different Database
SQL Server:Export Query as a .Txt File
Select Query with Date Condition
Is SQL Order by Clause Guaranteed to Be Stable ( by Standards)
List of Stored Procedure from Table
Generating Rows Based on Column Value
Case Statement in SQL, How to Return Multiple Variables