Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    1

    Unanswered: Full Outer Join in Access

    Hi,

    Hope someone can help me with this. I'm trying to do a full outer join in Access across 3 tables using a union query that someone recommended (or another method if it can be done):

    --TABLES--
    BOE
    INDICES
    TECHNICALS


    The common field among them is a date field in the fisrt column but the tables differ in terms of both the number of rows (dates) they have and also the number of columns/fields. Hence I want to bring back all rows from all three tables even those that are unmatched by the date field, but I want to join by date field across the tables so where there is a match, the data is aligned by the date field. I've been told I need to use a union query to do this but I am having problems.

    I ran the query below, but it generates a message saying that the "number of columns in the tables does not match. But I thought the whole point of a union query was to allow this! But I've never really used SQL for Access (only oracle at basic level) so not sure if the syntax is right.

    Hope someone can shed some light or suggest an alternative approach for what I need to do.

    Thanks

    Lucas

    SELECT *
    FROM
    BOE LEFT JOIN indices ON bedmate=indices.date
    UNION select *
    from
    technicals LEFT JOIN boe ON technicals.date=boe.date;

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Lucas

    There is no full outer join in Access.
    Quote Originally Posted by Lucaslondon
    I ran the query below, but it generates a message saying that the "number of columns in the tables does not match. But I thought the whole point of a union query was to allow this!
    Union queries (and the related difference and intersection) need to be "union compatible" which means that there must be the same number of columns and they must be the same data type (or at least be easily converted to the other). This is standard SQL and not specific to Access SQL. You will need, therefore, to list all the columns you need to use in your union query.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Although you can't use FULL OUTER JOIN syntax in Access you can replicate the same effect using 3 joins and UNION clauses.
    Quote Originally Posted by databasejournal.com
    Access does not support the FULL OUTER join clause. Therefore, to simulate the FULL OUTER join functionality you need perform three different joins, and then UNION together the three different result sets. Where Set 1 would be created using an INNER JOIN, Set 2 would be created with a LEFT OUTER JOIN and Set 3 would be created using a RIGHT OUTER JOIN.
    Source: DatabaseJournal.com
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •