Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unhappy Unanswered: how to do a full outer join in access 2002?

    I am trying to compare costs from 2 tables,
    the 2 tables are simler in structure but the number of rows are different in each.

    I need to get nulls for the mismatched rows in the resulting query

    estimated cost table:
    ---PK---
    Key month Ecost
    1........1.........200
    1........2.........300
    2........1.........100
    2........2.........100

    Actual cost table"
    ---PK---
    Key month Acost
    1 1 200
    1 2 300
    1 3 200
    1 4 300
    2 1 100

    I need a sql query to get a result like this:
    EKey Emonth AKey Amonth Ecost Acost
    1...........1..........1...........1...........200 200
    1...........2..........1...........2...........300 300
    -...........-...........1...........3...........- 200
    -...........-...........1...........4...........- 300
    2...........1..........2...........1...........100 100
    2...........2..........-...........-............100 -

    a FULL OUTER JOIN on EKey = AKey would give me this but it does not seem to work with Access 2002

    any help would be great!
    Last edited by kmittal; 01-15-04 at 18:11.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    full outer =
    (left outer)
    union all
    (right outer where left row is unmatched)
    Code:
    select EKey 
         , Emonth 
         , AKey 
         , Amonth 
         , Ecost 
         , Acost
      from Estimated E
    left outer
      join Actual A
        on EKey   = AKey
       and Emonth = Amonth
    union all   
    select EKey 
         , Emonth 
         , AKey 
         , Amonth 
         , Ecost 
         , Acost
      from Estimated E
    right outer
      join Actual A
        on EKey   = AKey
       and Emonth = Amonth
    where EKey is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    9
    thanks r937, I get the right rows now but how do you get nulls to show up as nulls?
    my report totals get inaccurate as the columns dont show up as 0 or null, rt now they show up as the same row above it,
    I tried the NZ() function too but no luck

  4. #4
    Join Date
    Jan 2004
    Posts
    9
    ok never mind, had an error in my join.
    it all works now
    Thanks!!!

Posting Permissions

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