Access 2003 Left Join with Subquery Confusion

[JeffK627]

I'm trying to use a Left Join to a subquery in Access 2003 and it isn't working as expected. When I run the subquery by itself it returns the expected results. Here's the subquery:

SELECT 
'T0901' AS TestNumber
, NAME AS TestName
, TUBE AS SOFTTube
, '0901 -- ' & [ID] & ' / ' & [NAME] AS TestID
, IIf([LEVEL]='I',0,-1) AS GroupTestIndicator
, ID AS TestMnemonic
, LOCATION AS TestingLocationID
, 0 AS PerformingLabID 
FROM TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS

But when I try to left join this to a table it doesn't return the Name, Tube, Level, ID or Location fields:

SELECT DISTINCT
Q01.TestNumber
, Q01.TestName
, Q01.SOFTTube
, Q01.TestID
, Q01.GroupTestIndicator
, Q01.TestMnemonic
, Q01.TestingLocationID
, Q01.PerformingLabID 
FROM ( 
SELECT 
'T0901' AS TestNumber
, NAME AS TestName
, TUBE AS SOFTTube
, '0901 -- ' & [ID] & ' / ' & [NAME] AS TestID
, IIf([LEVEL]='I',0,-1) AS GroupTestIndicator
, ID AS TestMnemonic
, 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;

I need it to return a result even if CTTest_TEST_MASTER.TestNumber is null, but if I add a Where clause like

WHERE CTTest_TEST_MASTER.TestNumber Is Null

at the end it returns nothing at all.

TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS only has one row at any given time.

Can anyone tell me what I'm doing wrong here?

 

[JeffK627]

OK, here's some sample data:

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',	'0')
('T0589',	'CULTURE, HSV',	'VTM',	'589 -- HSV / CULTURE, HSV',	'0',	'HSV',	'NULL',	'0',	'0')
('T0598',	'CULTURE, AFB, URINE',	'SCN',	'598 -- AFBUR / CULTURE, AFB, URINE',	'0',	'AFBUR',	'NULL',	'0',	'0')
('T0600',	'ANTI-GLIADIN AB',	'NULL',	'600 -- ANGLB / ANTI-GLIADIN AB',	'1',	'ANGLB',	'NULL',	'0',	'0')
('T0601',	'ANTI-DNA ANTIBODY (DS)',	'SRS',	'601 -- DNADS / ANTI-DNA ANTIBODY (DS)',	'0',	'DNADS',	'NULL',	'0',	'0')

 

[Joe4]

It looks your second query lists 8 fields in the "INSERT INTO" clause, but has 9 values in the VALUES listing. They need to match.

 

[JeffK627]

Oops, I see what you mean - you can just take the last '0' out of each line, that was a copy/paste error.

 

[Joe4]

No, I am talking about your code in post #10:

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',    '0')

Fields in INSERT INTO clause:
1. Testnumber
2. TestName
3. SOFTTube
4. TestID
5. GroupTestIndicator
6. TestMnemonic
7. TestingLocationID
8. PerformingLabID

Values listed in VALUES clause:
1. 'T0584'
2. 'CULTURE, HYPOPHARYNX'
3. 'SWB'
4. '584 -- HYPNX / CULTURE, HYPOPHARYNX'
5. '0'
6. 'HYPNX'
7. 'NULL'
8. '0'
9. '0'

The number of items in these two clauses need to match in order to have a valid SQL statement.
Try running the code you posted there yourself, and you will see the error message.

 

[JeffK627]

Right, I realized what you meant after I replied. I tried to edit, but you got in before I could. Just remove the last ", '0'" from each VALUES line, that was a copy error.

 

[Joe4]

OK, great. So I was able to load data into both tables now.

Just to confirm that I understand completely what you want to happen, let me know if I have this right:
- You want to insert the record in the "TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS" into the "CTTest_TEST_MASTER" table, using the T0934 code.
Is that right? Where does the "T0934" code come from? Is it any coincidence that it resembles the BarCode value?
- Any you want to check to make sure that a "T0934" record doesn't already exist in the table before inserting it?
If it does, then what?

 

[JeffK627]

OK, great. So I was able to load data into both tables now.

Just to confirm that I understand completely what you want to happen, let me know if I have this right:
- You want to insert the record in the "TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS" into the "CTTest_TEST_MASTER" table, using the T0934 code.
Is that right?

Yes, that's correct.

Where does the "T0934" code come from?

It's calculated from user input. The user selects a code from a list and the "T" is appended.

Is it any coincidence that it resembles the BarCode value?

The BarCode value is what the user enters, so no coincidence.

- Any you want to check to make sure that a "T0934" record doesn't already exist in the table before inserting it?

Yes.

If it does, then what?

Then it shouldn't enter a row. The "Where CTTest_TEST_MASTER Is Null" clause should prevent that - at least it would in SQL Server, which I know much more about than Access.

 

[Joe4]

OK. This seems to work:

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
WHERE 
    DCount("*","CTTest_TEST_MASTER","Testnumber='T0934'")=0;

Note that you might be able to avoid having to hard-code anything or use input and just use the Barcode to get the value, i.e.

SELECT 
    'T' & [TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS]![BARCODE] AS TestNumber,
    TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS.NAME AS TestName,
    TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS.TUBE AS SOFTTube, 
    [TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS]![BARCODE] & ' -- ' & [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
WHERE 
    DCount("*","CTTest_TEST_MASTER","Testnumber='T" & [BarCode] & "'")=0;

 

[JeffK627]

Thanks Joe4, that did it. And yes, I will be using the concatenation as you outlined, the TestNumber was just harcoded for testing purposes. Have a good holiday season!

 

[Joe4]

Excellent! Glad we were able to figure it out.