Access 2003 Left Join with Subquery Confusion

JeffK627

Active Member
Joined
Jun 22, 2005
Messages
313
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:

Code:
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:

Code:
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
Code:
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?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
Why are you doing a Subquery instead of just a straight up query between the two tables?
 

JeffK627

Active Member
Joined
Jun 22, 2005
Messages
313
Because I got a syntax error when I tried this:

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
When joining two tables, you should qualify each field with the table reference, like you did in the "ON" clause, i.e.
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
So were you able to get this sorted out?
 

JeffK627

Active Member
Joined
Jun 22, 2005
Messages
313
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:

Code:
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:
TestNumberTestNameSOFTTubeTestIDGroupTestIndicatorTestMnemonicTestingLocationIDPerformingLabIDTestNumber
T0934HEMATOLOGYLVV0934 -- H1686 / HEMATOLOGY-1H1686ELM0NULL

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
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

Active Member
Joined
Jun 22, 2005
Messages
313
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:

Column NameType
IDvarchar(5)
NAMEvarchar(60)
ACTIVEboolean
LOCATIONvarchar(4)
DepartmentVarchar(5)
BARCODEVarchar(4)
LEVELvarchar(1)
TUBEvarchar(3)

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

Column NameType
Testnumbervarchar(5)
TestNamevarchar(23)
SOFTTubevarchar(5)
TestIDvarchar(40)
GroupTestIndicatorboolean
TestMnemonicvarchar(5)
TestingLocationIDvarchar(5)
PerformingLabIDLong

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
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,118
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top