Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2014
    Location
    Rochester, NY Area
    Posts
    2

    Unanswered: Access 2003 Left Join with NULLs Returns No Rows

    Hello all, I'm new to this forum. I have a lot of experience with SQL Server but not as much with Access.

    I'm working with a legacy Access 2003 application and I'm having problems with the following query:
    Code:
    SELECT 
      Q01.TestNumber
    , Q01.TestName
    , Q01.SOFTTube
    , Q01.TestID
    , Q01.GroupTestIndicator
    , Q01.TestMnemonic
    , Q01.TestingLocationID
    , Q01.PerformingLabID 
    FROM ( 
    SELECT 
      'T0934' AS TestNumber
    , TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS.NAME AS TestName
    , TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS.TUBE AS SOFTTube
    , '0934 -- ' & [ID] & ' / ' & [NAME] AS TestID
    , IIf([LEVEL]='I',0,-1) AS GroupTestIndicator
    , TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS.ID AS TestMnemonic
    , TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS.LOCATION AS TestingLocationID
    , 0 AS PerformingLabID FROM TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS) AS Q01 
    LEFT JOIN CTTest_TEST_MASTER 
    ON Q01.TestNumber = CTTest_TEST_MASTER.TestNumber 
    WHERE (((CTTest_TEST_MASTER.TestNumber) Is Null));
    It should return a row even though there is no value 'T0934' in the TestNumber column in CTTest_TEST_MASTER, but it returns no rows. The query works in SQL Server (I have to change the IIf statement to a CASE but that's the only difference). Can anyone tell me why this query isn't returning the expected result?

    Here's the code to create the tables and load sample data:
    Code:
    CREATE TABLE [TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS]
        ( [ID] TEXT(5),
          [NAME] TEXT(59),
          [NAME_COLOR] IMAGE,
          [ACTIVE] TEXT(1),
          [LOCATION] TEXT(4),
          [DEPARTMENT] TEXT(5),
          [WORKSTATION] TEXT(5),
          [BARCODE] TEXT(4),
          [BARCODE_COLOR] IMAGE,
          [LEVEL] TEXT(1),
          [TUBE] TEXT(3)
        );
    
    CREATE TABLE [CTTest_TEST_MASTER]
        ( [TestNumber] TEXT(5),
          [TestName] TEXT(23),
          [SOFTTube] TEXT(5),
          [TestID] TEXT(40),
          [GroupTestIndicator] BIT,
          [TestMnemonic] TEXT(5),
          [TestingLocationID] TEXT(5),
          [PerformingLabID] INTEGER
        );
    
    INSERT INTO TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS(ID, NAME, ACTIVE, LOCATION, Department, BARCODE, LEVEL, TUBE)
    VALUES('H1686', 'HEMATOLOGY', '1', 'ELM', '0', '0934', 'G', 'LVV', )
    
    INSERT INTO CTTest_TEST_MASTER( Testnumber, TestName, SOFTTube, TestID, GroupTestIndicator, TestMnemonic, TestingLocationID, PerformingLabID)
    VALUES('T0584', 'CULTURE, HYPOPHARYNX', 'SWB', '584 -- HYPNX / CULTURE, HYPOPHARYNX', '0', 'HYPNX', 'NULL', '0')
    ('T0589', 'CULTURE, HSV', 'VTM', '589 -- HSV / CULTURE, HSV', '0', 'HSV', 'NULL', '0')
    ('T0598', 'CULTURE, AFB, URINE', 'SCN', '598 -- AFBUR / CULTURE, AFB, URINE', '0', 'AFBUR', 'NULL', '0')
    ('T0600', 'ANTI-GLIADIN AB', 'NULL', '600 -- ANGLB / ANTI-GLIADIN AB', '1', 'ANGLB', 'NULL', '0')
    ('T0601', 'ANTI-DNA ANTIBODY (DS)', 'SRS', '601 -- DNADS / ANTI-DNA ANTIBODY (DS)', '0', 'DNADS', 'NULL', '0')

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Strange indeed!

    It seems that the use of a litteral ('T0934') prevents Access from correctly processing the junction. You'll get the expected result if you try with:
    Code:
    SELECT 
      Q01.TestNumber
    , Q01.TestName
    , Q01.SOFTTube
    , Q01.TestID
    , Q01.GroupTestIndicator
    , Q01.TestMnemonic
    , Q01.TestingLocationID
    , Q01.PerformingLabID 
    FROM ( 
    SELECT 
      ID AS TestNumber
    , TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS.NAME AS TestName
    , TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS.TUBE AS SOFTTube
    , '0934 -- ' & [ID] & ' / ' & [NAME] AS TestID
    , IIf([LEVEL]='I',0,-1) AS GroupTestIndicator
    , TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS.ID AS TestMnemonic
    , TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS.LOCATION AS TestingLocationID
    , 0 AS PerformingLabID FROM TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS) AS Q01 
    LEFT JOIN CTTest_TEST_MASTER 
    ON Q01.TestNumber = CTTest_TEST_MASTER.TestNumber 
    WHERE (((CTTest_TEST_MASTER.TestNumber) Is Null));
    Keep in mind that Access uses its own dialect of the SQL language (Access SQL is not T-SQL). This can lead to very subtle errors such as a query that is syntactically correct both in Access and in T-SQL but that returns a different result set.
    Have a nice day!

  3. #3
    Join Date
    Dec 2014
    Location
    Rochester, NY Area
    Posts
    2
    Thanks Sinndho, that was indeed the problem. Ironically I was only using the literal to test the query, in practice that value will be the BARCODE field from TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS with a 'T' appended to the front. Strange little Access idiosyncrasy...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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