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?

 

[Joe4]

Why are you doing a Subquery instead of just a straight up query between the two tables?

 

[JeffK627]

Because I got a syntax error when I tried this:

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;

 

[Joe4]

When joining two tables, you should qualify each field with the table reference, like you did in the "ON" clause, i.e.

ON [COLOR=#ff0000]Q01.[/COLOR]TestNumber = [COLOR=#ff0000]CTTest_TEST_MASTER.[/COLOR]TestNumber;

So each non-hard-coded in your SELECT clause should be qualified, i.e. which tables do NAME, TUBE, ID, LEVEL, and LOCATION come from?

Also, you may have a few conflicts going on.
1. You have a field called "NAME". You should never used reserved words (like those used for Functions, Properties, and Methods) as the name of fields. This can lead to confusion and problems.
2. In the query, your are assigning the value 'T0901' to a calculated field called "TestNumber". Then it appears that you are trying to use "TestNumber" in your Join. Is this the same field? If so, this isn't the way you want to do it. Are these tables really related at all? If so, by what field (it cannot be "TestNumber" if it doesn't already exist in each table)?

It may be helpful and clearer if you can post small data samples of each table, and then show what your expected results should look like.

 

[Joe4]

So were you able to get this sorted out?

 

[JeffK627]

Not yet. In response to your points:

1. Unfortunately I'm stuck with column names like "NAME", this is a legacy tool and I can't change the column names.
2. The value for TestNumber shouldn't be in the CTTest_TEST_MASTER table. The point of the LEFT JOIN where CTTest_TEST_MASTER.TestNumber Is Null is ultimately to insert these values into CTTest_TEST_MASTER, but ONLY if the given TestNumber isn't already in there. The value of TestNumber is calculated from user input, so it may vary.
3. TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS only has one row populated at any given time.

The full query as it appears in Access is:

SELECT 
  Q01.TestNumber
, Q01.TestName
, Q01.SOFTTube
, Q01.TestID
, Q01.GroupTestIndicator
, Q01.TestMnemonic
, Q01.TestingLocationID
, Q01.PerformingLabID
, CTTest_TEST_MASTER.TestNumber
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));

CTTest_TEST_MASTER is a linked table in Access that links to a table named TEST_MASTER on a SQL Server. If I create a temp table in SQL Server to take the place of TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS and run this query against it and the TEST_MASTER table (had to change the IIf statement of course, but that's the only change) it returns the expected result, which is one row that looks like this:
[table="width: 500, class: grid, align: left"]
[tr]
[td]TestNumber[/td]
[td]TestName[/td]
[td]SOFTTube[/td]
[td]TestID[/td]
[td]GroupTestIndicator[/td]
[td]TestMnemonic[/td]
[td]TestingLocationID[/td]
[td]PerformingLabID[/td]
[td]TestNumber[/td]
[/tr]
[tr]
[td]T0934[/td]
[td]HEMATOLOGY[/td]
[td]LVV[/td]
[td]0934 -- H1686 / HEMATOLOGY[/td]
[td]-1[/td]
[td]H1686[/td]
[td]ELM[/td]
[td]0[/td]
[td]NULL[/td]
[/tr]
[/table]

But in Access it returns nothing. I've checked and TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS has the row of data it's supposed to have, and the TestNumber T0934 does not appear in CTTest_TEST_MASTER. As far as I can see it should be returning that same row as it does in SQL Server, but it's not.

I hope I've provided enough information. Any help appreciated!

 

[Joe4]

I am having a hard time envisioning all this without being able to see samples of what the data in these tables looks like, along with the expected result.

 

[JeffK627]

OK, I already provided the expected result.

TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS only ever has one row populated at any time. It has these columns:

[table="width: 500"]
[tr]
[td]Column Name[/td]
[td]Type[/td]
[/tr]
[tr]
[td]ID[/td]
[td]varchar(5)[/td]
[/tr]
[tr]
[td]NAME[/td]
[td]varchar(60)[/td]
[/tr]
[tr]
[td]ACTIVE[/td]
[td]boolean[/td]
[/tr]
[tr]
[td]LOCATION[/td]
[td]varchar(4)[/td]
[/tr]
[tr]
[td]Department[/td]
[td]Varchar(5)[/td]
[/tr]
[tr]
[td]BARCODE[/td]
[td]Varchar(4)[/td]
[/tr]
[tr]
[td]LEVEL[/td]
[td]varchar(1)[/td]
[/tr]
[tr]
[td]TUBE[/td]
[td]varchar(3)[/td]
[/tr]
[/table]

CTTest_TEST_MASTER is a linked table to a SQL Server database with the following columns:

[table="width: 500"]
[tr]
[td]Column Name[/td]
[td]Type[/td]
[/tr]
[tr]
[td]Testnumber[/td]
[td]varchar(5)[/td]
[/tr]
[tr]
[td]TestName[/td]
[td]varchar(23)[/td]
[/tr]
[tr]
[td]SOFTTube[/td]
[td]varchar(5)[/td]
[/tr]
[tr]
[td]TestID[/td]
[td]varchar(40)[/td]
[/tr]
[tr]
[td]GroupTestIndicator[/td]
[td]boolean[/td]
[/tr]
[tr]
[td]TestMnemonic[/td]
[td]varchar(5)[/td]
[/tr]
[tr]
[td]TestingLocationID[/td]
[td]varchar(5)[/td]
[/tr]
[tr]
[td]PerformingLabID[/td]
[td]Long[/td]
[/tr]
[/table]

The outer SELECT statement in my previous post is actually part of an INSERT into CTTest_TEST_MASTER. It's meant to insert a new TestNumber, hence the LEFT JOIN ON Q01.TestNumber = CTTest_TEST_MASTER.TestNumber where CTTest_TEST_MASTER.TestNumber Is Null. This should "force" a row with the TestNumber from the subquery (aliased as Q01) and the other columns in the query because

SELECT <columns> 
FROM TableA AS A 
LEFT JOIN TableB AS B 
ON A.Key = B.KEY 
WHERE B.Key IS NULL

should return a row with NULL in the column for B.Key.

I have confirmed that the TestNumber in question does not exist in the CTTest_TEST_MASTER table and that the single row is correctly populated in TESTSETUP_STAGING_INDIVIDUALTEST_INFO_FROM_LIS. The query returns the expected result (a single row) as shown in my previous post when I run it in SQL Server, but when run in Access it returns nothing.

Does that help?

 

[Joe4]

Sort of, I'll see what I can do with it.

What I am trying to do is recreate your database on my side, so I was really hoping for some sample data in each of these tables. That way I can try to mirror your situation, as much as possible. These things are so much easier when we have a copy of the database to play around with. So if I can't get one, I try to create one.

 

[Joe4]

OK. In testing various things, it looks like you cannot do a LEFT JOIN in Access from a Table/Query where the Field on the Left side of the Join is Null.