Results 1 to 12 of 12
  1. #1
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162

    Full outer join in Access.

    Hi.


    Access does not seem to support Full outer join like Sql Server. So, is there any way around it?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes. Create a left join and a right join then combine using a UNION query.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Access does outer joins.
    dbl click the join,
    select which table to show all records.

  4. #4
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by ranman256 View Post
    Access does outer joins.
    dbl click the join,
    select which table to show all records.
    That is not a full outer join.

  5. #5
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by isladogs View Post
    Yes. Create a left join and a right join then combine using a UNION query.
    But that does not have the same effect as Full Outer join. Union queries just appends the rows. They don't show the null values.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by bubai View Post
    But that does not have the same effect as Full Outer join. Union queries just appends the rows. They don't show the null values.
    Sorry but you're wrong.
    First of all a union query doesn't append anything. Its just two or more select queries run in turn.
    Secondly if constructed correctly it does exactly what a full outer join does in e.g. sql server.

    See this link if my meaning isn't clear https://datatofish.com/full-outer-join-in-access/
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by isladogs View Post
    Sorry but you're wrong.
    First of all a union query doesn't append anything. Its just two or more select queries run in turn.
    Secondly if constructed correctly it does exactly what a full outer join does in e.g. sql server.

    See this link if my meaning isn't clear https://datatofish.com/full-outer-join-in-access/
    Thanks for the link. It worked for the two column tables. But I'm not sure if it will work for tables with more than two columns.
    Last edited by bubai; 11-20-2019 at 04:37 AM.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by bubai View Post
    I'm not sure if it will work for tables with more than two tables.
    That doesn't make sense.
    If you can't work out the answer suggest you post a cut down database with sample data to explain your point. Also state what result you would expect
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by isladogs View Post
    That doesn't make sense.
    If you can't work out the answer suggest you post a cut down database with sample data to explain your point. Also state what result you would expect
    Sorry I made a typo. What I meant was- I'm not sure if it will work for tables with more than two columns. But it does. However my observation is that if any of the queries with Left join or Right join join contains Null values then it does not work and returns duplicates.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I think that would be true in sql server as well. A null is not equal to anything, not even another null.
    You might consider using the Nz function in both the left join and right join parts

    If that still doesn't help, as I requested before, please upload a cut down database with sample data to show your issue.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by isladogs View Post
    I think that would be true in sql server as well. A null is not equal to anything, not even another null.
    You might consider using the Nz function in both the left join and right join parts

    If that still doesn't help, as I requested before, please upload a cut down database with sample data to show your issue.
    Thanks a lot for your help.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome. Did using Nz solve the duplicate problem?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Full outer join in Access
    By johnseito in forum Queries
    Replies: 1
    Last Post: 07-14-2019, 04:05 PM
  2. Access SQL - FULL OUTER JOIN
    By johnseito in forum Queries
    Replies: 3
    Last Post: 08-08-2017, 08:26 AM
  3. Full Outer Join -- How to?
    By WesHarding in forum Queries
    Replies: 5
    Last Post: 03-20-2017, 10:40 AM
  4. Replies: 4
    Last Post: 12-22-2014, 08:48 PM
  5. I can't get a full outer join to work
    By Bobt1993 in forum Queries
    Replies: 3
    Last Post: 03-20-2010, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums