Results 1 to 5 of 5
  1. #1
    keyel1971 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    7

    Left join not working like usual


    I have a left table and a right table. I'm left joined on field X. I want all field X values and if there isn't a corresponding field X value in the right table, then I want nulls. I have 3 where clauses on the right table, but I included "Or Is Null" on each of them. This has always worked 100% fine when I've done it in other DBs, but for some reason it's not working here. One of the criteria is based on a field from a form, but as I said, I've used this identical logic before and it works fine. Anyone every encounter something similar? Thanks in advanced.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Post the SQL statement or attach db for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    keyel1971 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    7
    Here's the SQL.....as I said, I've re-used this many times in many databases, so it has to be something with the table, but I'm at a loss as to what --- the left table (tbl_EMSetal_ProdRef) is the same as it's always been the other times I've used it.
    INSERT INTO tbl_BO_CIC_EMSetal_pend ( Product, PendClts, PendAmt )
    SELECT tbl_EMSetal_ProdRef.Product, Count(tbl_pending_queue_historical.cur_amount) AS [Count], Sum(nz([cur_amount],0)) AS Amount
    FROM tbl_EMSetal_ProdRef LEFT JOIN tbl_pending_queue_historical ON tbl_EMSetal_ProdRef.Product = tbl_pending_queue_historical.txt_product
    WHERE (((tbl_pending_queue_historical.txt_department)="P IRS" Or (tbl_pending_queue_historical.txt_department) Is Null) AND ((tbl_pending_queue_historical.ysn_exclude)=0 Or (tbl_pending_queue_historical.ysn_exclude) Is Null) AND ((tbl_pending_queue_historical.dte_reporting_month ) Is Null Or (tbl_pending_queue_historical.dte_reporting_month) =[Forms]![FormMaster]![RepMo]))
    GROUP BY tbl_EMSetal_ProdRef.Product;

  4. #4
    keyel1971 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    7
    Sorry guys.....first timer here! In the process of updating the other sites I posted on stating that this is solved and that I'm sorry for taking your time.....as this is something basic I should've caught myself!

    Hopefully I can be of value in here at some point in the future!

  5. #5
    keyel1971 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    7
    Oh- the problem was blanks versus nulls!

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

Similar Threads

  1. Left Join not
    By j_a_monk in forum Queries
    Replies: 5
    Last Post: 08-07-2011, 09:47 AM
  2. inner, left or right join ?
    By toqilula in forum Access
    Replies: 0
    Last Post: 04-11-2011, 12:20 AM
  3. Replies: 3
    Last Post: 02-02-2011, 01:00 PM
  4. Left join problem
    By e.badin in forum Queries
    Replies: 5
    Last Post: 01-17-2011, 08:03 AM
  5. Update with LEFT JOIN
    By mcarthey in forum Access
    Replies: 1
    Last Post: 08-27-2008, 10:49 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