outer join

[Eitan M]

Hello,
How can I do outer join in ms-access database ?

Thanks :)

[SpookiePower]

"Eitan M" <no_spam_please@nospam_please.com> skrev i en meddelelse news:e4**********@news2.netvision.net.il...

Hello,
How can I do outer join in ms-access database ?

Thanks :)

Outer join, is the same at Left Join & Right Join.
You have to use on of these.

[Jamie Collins]

SpookiePower wrote:

"Eitan M" <no_spam_please@nospam_please.com> skrev i en meddelelse news:e4**********@news2.netvision.net.il...

Hello,
How can I do outer join in ms-access database ?

Thanks :)

Outer join, is the same at Left Join & Right Join.
You have to use on of these.

LEFT JOIN and RIGHT JOIN are proprietary contractions of LEFT OUTER
JOIN and RIGHT OUTER JOIN from Standard SQL. There is also FULL OUTER
JOIN which Access/Jet does not support but can be mimicked with a LEFT
OUTER JOIN ...UNION...RIGHT OUTER JOIN.

Note Access/Jet does not fully support the ANSI join syntax and one
will commonly get the error 'Join expression not supported' e.g.

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

which may not be the same as

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

Again, one must work around this Jet limitation using a derived table.

Jamie.

--

[Allen Browne]

Create a query that uses the tables you want to join.

If Access does not join the tables for you, in the upper pane of the query
design window, drag the field from one table onto the matching field in the
other table. Access will display the join as a line betweeen the 2 fields.

Double-click the line. You see a dialog with 3 options, representing the
INNER JOIN, LEFT JOIN, and RIGHT JOIN.

Or, if you are a SQL buff, you can switch the query to SQL View (View menu
in query design), and hammer away on the keyboard to your heart's content.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eitan M" <no_spam_please@nospam_please.com> wrote in message
news:e4**********@news2.netvision.net.il...

How can I do outer join in ms-access database ?

[Lyle Fairfield]

Another whimsical way.

Link your local SQL-SERVER (SQLExpess?) to the Access DB. I find it's
better to do this outside Access and not to be saddled or addled with
whatever Access wants to add to the creation, such as Views to all the
Access DB's tables.

Create a new ADP connected to that Server with a new SQL DB, or just
use an existing one.

Create the SPROC in text eg:

ALTER PROCEDURE StoredProcedure1
AS
select p.productid, s.companyname from northwind...products p full
outer join northwind...suppliers s on p.supplierid = s.supplierid;

northwind is the name of the linked Access DB server.

While this many be unnecessary as there are ways to fudge the full
outer join in JET (as you have pointed out) one !!!SEEMS!!! to get the
full power of T_SQL and Access ADP for an MDB this way.

[onedaywhen]

Lyle Fairfield wrote:

Another whimsical way.

Link your local SQL-SERVER (SQLExpess?) to the Access DB ... <<snipped>>

What do you mean by 'another' and who mentioned SQL Server <g>?

Jamie.

--