Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

outer join

Discussion in 'Information Technology' started by Eitan M, Jul 28, 2006.

Tags:
  1. Eitan M

    Eitan M
    Expand Collapse
    Guest

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

    Thanks :)
     
  2. Loading...

    Similar Threads Forum Date
    Inner and Outer Storms Spiritual Articles Dec 16, 2005
    Pentagon Plans to Close 180 Sites, Shift Area Jobs to Outer Suburbs (washingtonpost.com) Interfaith Dialogues May 15, 2005
    Sone Bhambhani Joins Sikh Philosophy Network! New SPN'ers Thursday at 9:11 AM
    Gigi S Joins Sikh Philosophy Network! New SPN'ers Wednesday at 10:44 AM
    Khalis International Joins Sikh Philosophy Network! New SPN'ers Nov 23, 2016

  3. SpookiePower

    SpookiePower
    Expand Collapse
    Guest

    "Eitan M" <no_spam_please@nospam_please.com> skrev i en meddelelse news:e4s7iv$47r$1@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.
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    SpookiePower wrote:
    > "Eitan M" <no_spam_please@nospam_please.com> skrev i en meddelelse news:e4s7iv$47r$1@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.

    --
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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:e4s7iv$47r$1@news2.netvision.net.il...
    > How can I do outer join in ms-access database ?
     
  6. Lyle Fairfield

    Lyle Fairfield
    Expand Collapse
    Guest

    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.
     
  7. onedaywhen

    onedaywhen
    Expand Collapse
    Guest

    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.

    --
     

Share This Page