Results 1 to 8 of 8
  1. #1
    supak6 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2017
    Posts
    4

    Left join 2 queries, second query is result of another query where filtering is applied

    Hi guys.

    I am new here and comes with relatively simple question:-)
    I am trying to do very simple exercise:



    - I have query (name it "query 1". It contains customer details) and want to apply left join to it using another query (name it "query 2". It contains except other things residency).
    - query 2 is result of another queries (let's say query A and query B). Query A contains all residency countries. Query B only change Query A to following 4 options :
    US, CA, UK and XX. XX means all other countries.
    Query 2, which I use for joining with query 1, has exactly the same results as Query B...I just filter US, CA and UK. So XX option is omitted and filtered out.

    Now I want to use left join (Query 1 - Query 2) and map to every customer his/her residency. In case some customer should have residency US, CA or UK, it's mapped correctly.
    BUT in case some customer should have different country, IT DOESN'T MAP BLANK CELL (as I would expect) but it map to him/her XX option !!!

    Why XX option is mapped to Query 1 (there should be blank cell, or ?), if XX was filtered out previously and is not part of Query 2 output ?

    thanks a lot for help.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What you are saying is: select all customers with US, CA or UK, and also include all customers who don't belong in one of those three - including XX. You need to define the exact results you are trying to get to.

  3. #3
    supak6 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2017
    Posts
    4
    Quote Originally Posted by aytee111 View Post
    What you are saying is: select all customers with US, CA or UK, and also include all customers who don't belong in one of those three - including XX. You need to define the exact results you are trying to get to.
    thank you for answer. Yes, but why to those customers who are not from US, CA or UK Access just add blank cell ? I don't want to put there "XX". that's why I filtered out XX option previously, in Query B. So Query 2 doesn't contain any XX option. But when I join it to Query 1, it somehows appears again. So it's like access still have this XX as one of valid options and is prepared to join it to Query 1 rows.

    Please, what do you mean by "You need to define the exact results you are trying to get to" ?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your criteria is in query b, not in your final query - Access doesn't know about previous queries, all it knows is what it has right now, in this query. You will need to apply all criteria in this query that you need for your final results.

    What do you mean by "just add blank cell"? Are you looking for customers with a blank value? Cell is an Excel word, not Access.

  5. #5
    supak6 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2017
    Posts
    4
    Quote Originally Posted by aytee111 View Post
    Your criteria is in query b, not in your final query - Access doesn't know about previous queries, all it knows is what it has right now, in this query. You will need to apply all criteria in this query that you need for your final results.

    What do you mean by "just add blank cell"? Are you looking for customers with a blank value? Cell is an Excel word, not Access.
    Right, because if I do filtering in final query (result of Query1-Query2 left join), I would eliminate many rows. I want to keep all rows in Query 1 and to add to it residence column from Query 2. When customer is from UK, US or CA...then these options are added. If no, then blank record is added. But Access is adding XX.

    Also access knows about all other previous queries which lead to creation of Query 2, or ? Because Query 2 is results of previous queries.
    If not, then we would always have to do everything in one query and to join it with some another query.

    I will try to post today real example here. I understand that to describe this matter only in words can be confusing.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Yes, posting some example data will help. Can you post your db with some sample data? "Blank record is added" - is this an append query and you want to add a blank record?

    Maybe what you need is an IIF statement: IIF the country does not exist in query2 then set country = "", otherwise use the country that is on query2.
    CountryofResidence: IIf(IsNull(Query2.Country),"",Query2.Country)

  7. #7
    supak6 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2017
    Posts
    4
    Hi. So funny thing is that I have created similar scenario as described above. And in this case, it is working. I share the file here.

    Goal was to add "country" column from Query 2 to "customer" column in Query 1. It works, because when it can't find customer, country is blank.

    But we have different query (exactly the same case), which I can't share here due to sensitive data :-( Difference is that in that query, when Query 2 doesn't contain customer from Query 1, IT'S NOT BLANK, BUT 'XX' is added. But 'XX' option was filtered out in Query 2. Once it's filtered out why 'XX' option is still valid for MS Access and why query result (after LEFT JOIN query 1 with query 2) contains countries with 'XX' option ?
    Attached Files Attached Files

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It is not possible. If, as you say, query 2 does not have XX anywhere, then where does Access get it from? It is not taking it out of the air! Are you sure you are joining with the correct query?

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

Similar Threads

  1. Access queries for left join
    By kiranair in forum Access
    Replies: 16
    Last Post: 05-26-2016, 07:14 AM
  2. Replies: 17
    Last Post: 01-26-2014, 06:53 AM
  3. Replies: 4
    Last Post: 01-23-2014, 03:07 PM
  4. Left Join Query Issue - MS Access
    By mkc80 in forum Queries
    Replies: 1
    Last Post: 08-07-2013, 04:17 PM
  5. Replies: 7
    Last Post: 09-06-2012, 06:04 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