Left Join is excluding data from left table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This one is driving me nuts.

I am joining a table with a query on six fields to get to a unique row level
join. The table is the complete record set and the query is a subset with
some additional calculations. When I left join the six fields the resulting
set excludes the unmatched data which it shouldn't. I am running Access 2003
SP2.

Any help?

Thanks,

Tom.
 
Hard to say without seeing the query, but if you apply criteria against
any of the fields in the table on the right, the left join is negated
and basically becomes an inner join.

You can sometimes work around that by using the criteria against the
field along with Is Null

WHERE RightTable.SomeField = "Some Value" or RightTable.SomeField is Null

If that fails you need to use either a subquery or nested queries.

Basically a query that gets the records you want in the right table and
then use that query in the join.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Here it is

SELECT
Sum([AA]/6) AS Expr1

FROM
[260 Corrected FICL MF Assets] LEFT JOIN TAFees ON
([260 Corrected FICL MF Assets].SubLOB = TAFees.SubLOB) AND
([260 Corrected FICL MF Assets].SeriesCode = TAFees.Series) AND
([260 Corrected FICL MF Assets].MFCode = TAFees.TACode) AND
([260 Corrected FICL MF Assets].DRChanCode = TAFees.DRChanCode) AND
([260 Corrected FICL MF Assets].FirmCode = TAFees.FirmCode) AND
([260 Corrected FICL MF Assets].Month = TAFees.Month)

WHERE
((Not ([260 Corrected FICL MF Assets].SeriesCode)="SA") AND
(Not ([260 Corrected FICL MF Assets].SubLOB)="FRS") AND
(([260 Corrected FICL MF Assets].Month) Between "200607" And "200612"));
 
Which table is [AA] to be pulled from?
--
KARL DEWEY
Build a little - Test a little


Tom Telford said:
Here it is

SELECT
Sum([AA]/6) AS Expr1

FROM
[260 Corrected FICL MF Assets] LEFT JOIN TAFees ON
([260 Corrected FICL MF Assets].SubLOB = TAFees.SubLOB) AND
([260 Corrected FICL MF Assets].SeriesCode = TAFees.Series) AND
([260 Corrected FICL MF Assets].MFCode = TAFees.TACode) AND
([260 Corrected FICL MF Assets].DRChanCode = TAFees.DRChanCode) AND
([260 Corrected FICL MF Assets].FirmCode = TAFees.FirmCode) AND
([260 Corrected FICL MF Assets].Month = TAFees.Month)

WHERE
((Not ([260 Corrected FICL MF Assets].SeriesCode)="SA") AND
(Not ([260 Corrected FICL MF Assets].SubLOB)="FRS") AND
(([260 Corrected FICL MF Assets].Month) Between "200607" And "200612"));


KARL DEWEY said:
Post your SQL statement.
Click to expand...
Click to expand...
 
260 Corrected FICL MF Assets

Tom.

KARL DEWEY said:
Which table is [AA] to be pulled from?
--
KARL DEWEY
Build a little - Test a little


Tom Telford said:
Here it is

SELECT
Sum([AA]/6) AS Expr1

FROM
[260 Corrected FICL MF Assets] LEFT JOIN TAFees ON
([260 Corrected FICL MF Assets].SubLOB = TAFees.SubLOB) AND
([260 Corrected FICL MF Assets].SeriesCode = TAFees.Series) AND
([260 Corrected FICL MF Assets].MFCode = TAFees.TACode) AND
([260 Corrected FICL MF Assets].DRChanCode = TAFees.DRChanCode) AND
([260 Corrected FICL MF Assets].FirmCode = TAFees.FirmCode) AND
([260 Corrected FICL MF Assets].Month = TAFees.Month)

WHERE
((Not ([260 Corrected FICL MF Assets].SeriesCode)="SA") AND
(Not ([260 Corrected FICL MF Assets].SubLOB)="FRS") AND
(([260 Corrected FICL MF Assets].Month) Between "200607" And "200612"));


KARL DEWEY said:
Post your SQL statement.
--
KARL DEWEY
Build a little - Test a little


:

This one is driving me nuts.

I am joining a table with a query on six fields to get to a unique row level
join. The table is the complete record set and the query is a subset with
some additional calculations. When I left join the six fields the resulting
set excludes the unmatched data which it shouldn't. I am running Access 2003
SP2.

Any help?

Thanks,

Tom.
Click to expand...
Click to expand...
Click to expand...
 
This adds the "Is Null" as John said.

SELECT Sum([260 Corrected FICL MF Assets].[AA]/6) AS Expr1

FROM [260 Corrected FICL MF Assets] LEFT JOIN TAFees ON ([260 Corrected FICL
MF Assets].SubLOB = TAFees.SubLOB OR TAFees.SubLOB Is Null) AND ([260
Corrected FICL MF Assets].SeriesCode = TAFees.Series OR TAFees.Series Is
Null) AND ([260 Corrected FICL MF Assets].MFCode = TAFees.TACode) AND ([260
Corrected FICL MF Assets].DRChanCode = TAFees.DRChanCode) AND ([260 Corrected
FICL MF Assets].FirmCode = TAFees.FirmCode) AND ([260 Corrected FICL MF
Assets].Month = TAFees.Month OR TAFees.Month Is Null)

WHERE ((Not ([260 Corrected FICL MF Assets].SeriesCode)="SA") AND (Not ([260
Corrected FICL MF Assets].SubLOB)="FRS") AND (([260 Corrected FICL MF
Assets].Month) Between "200607" And "200612"));

--
KARL DEWEY
Build a little - Test a little


Tom Telford said:
Here it is

SELECT
Sum([AA]/6) AS Expr1

FROM
[260 Corrected FICL MF Assets] LEFT JOIN TAFees ON
([260 Corrected FICL MF Assets].SubLOB = TAFees.SubLOB) AND
([260 Corrected FICL MF Assets].SeriesCode = TAFees.Series) AND
([260 Corrected FICL MF Assets].MFCode = TAFees.TACode) AND
([260 Corrected FICL MF Assets].DRChanCode = TAFees.DRChanCode) AND
([260 Corrected FICL MF Assets].FirmCode = TAFees.FirmCode) AND
([260 Corrected FICL MF Assets].Month = TAFees.Month)

WHERE
((Not ([260 Corrected FICL MF Assets].SeriesCode)="SA") AND
(Not ([260 Corrected FICL MF Assets].SubLOB)="FRS") AND
(([260 Corrected FICL MF Assets].Month) Between "200607" And "200612"));


KARL DEWEY said:
Post your SQL statement.
Click to expand...
Click to expand...
 
I understand the change but that still leaves me wondering what in the query
is preventing the unmatched records not to show up.

Thanks for the help.

Tom.

KARL DEWEY said:
This adds the "Is Null" as John said.

SELECT Sum([260 Corrected FICL MF Assets].[AA]/6) AS Expr1

FROM [260 Corrected FICL MF Assets] LEFT JOIN TAFees ON ([260 Corrected FICL
MF Assets].SubLOB = TAFees.SubLOB OR TAFees.SubLOB Is Null) AND ([260
Corrected FICL MF Assets].SeriesCode = TAFees.Series OR TAFees.Series Is
Null) AND ([260 Corrected FICL MF Assets].MFCode = TAFees.TACode) AND ([260
Corrected FICL MF Assets].DRChanCode = TAFees.DRChanCode) AND ([260 Corrected
FICL MF Assets].FirmCode = TAFees.FirmCode) AND ([260 Corrected FICL MF
Assets].Month = TAFees.Month OR TAFees.Month Is Null)

WHERE ((Not ([260 Corrected FICL MF Assets].SeriesCode)="SA") AND (Not ([260
Corrected FICL MF Assets].SubLOB)="FRS") AND (([260 Corrected FICL MF
Assets].Month) Between "200607" And "200612"));

--
KARL DEWEY
Build a little - Test a little


Tom Telford said:
Here it is

SELECT
Sum([AA]/6) AS Expr1

FROM
[260 Corrected FICL MF Assets] LEFT JOIN TAFees ON
([260 Corrected FICL MF Assets].SubLOB = TAFees.SubLOB) AND
([260 Corrected FICL MF Assets].SeriesCode = TAFees.Series) AND
([260 Corrected FICL MF Assets].MFCode = TAFees.TACode) AND
([260 Corrected FICL MF Assets].DRChanCode = TAFees.DRChanCode) AND
([260 Corrected FICL MF Assets].FirmCode = TAFees.FirmCode) AND
([260 Corrected FICL MF Assets].Month = TAFees.Month)

WHERE
((Not ([260 Corrected FICL MF Assets].SeriesCode)="SA") AND
(Not ([260 Corrected FICL MF Assets].SubLOB)="FRS") AND
(([260 Corrected FICL MF Assets].Month) Between "200607" And "200612"));


KARL DEWEY said:
Post your SQL statement.
--
KARL DEWEY
Build a little - Test a little


:

This one is driving me nuts.

I am joining a table with a query on six fields to get to a unique row level
join. The table is the complete record set and the query is a subset with
some additional calculations. When I left join the six fields the resulting
set excludes the unmatched data which it shouldn't. I am running Access 2003
SP2.

Any help?

Thanks,

Tom.
Click to expand...
Click to expand...
Click to expand...
 
If you examine the SQL it is saying the joins must be equal even though it is
a left join. Therefore you need to include an OR Is Null statement.
--
KARL DEWEY
Build a little - Test a little


Tom Telford said:
I understand the change but that still leaves me wondering what in the query
is preventing the unmatched records not to show up.

Thanks for the help.

Tom.

KARL DEWEY said:
This adds the "Is Null" as John said.

SELECT Sum([260 Corrected FICL MF Assets].[AA]/6) AS Expr1

FROM [260 Corrected FICL MF Assets] LEFT JOIN TAFees ON ([260 Corrected FICL
MF Assets].SubLOB = TAFees.SubLOB OR TAFees.SubLOB Is Null) AND ([260
Corrected FICL MF Assets].SeriesCode = TAFees.Series OR TAFees.Series Is
Null) AND ([260 Corrected FICL MF Assets].MFCode = TAFees.TACode) AND ([260
Corrected FICL MF Assets].DRChanCode = TAFees.DRChanCode) AND ([260 Corrected
FICL MF Assets].FirmCode = TAFees.FirmCode) AND ([260 Corrected FICL MF
Assets].Month = TAFees.Month OR TAFees.Month Is Null)

WHERE ((Not ([260 Corrected FICL MF Assets].SeriesCode)="SA") AND (Not ([260
Corrected FICL MF Assets].SubLOB)="FRS") AND (([260 Corrected FICL MF
Assets].Month) Between "200607" And "200612"));

--
KARL DEWEY
Build a little - Test a little


Tom Telford said:
Here it is

SELECT
Sum([AA]/6) AS Expr1

FROM
[260 Corrected FICL MF Assets] LEFT JOIN TAFees ON
([260 Corrected FICL MF Assets].SubLOB = TAFees.SubLOB) AND
([260 Corrected FICL MF Assets].SeriesCode = TAFees.Series) AND
([260 Corrected FICL MF Assets].MFCode = TAFees.TACode) AND
([260 Corrected FICL MF Assets].DRChanCode = TAFees.DRChanCode) AND
([260 Corrected FICL MF Assets].FirmCode = TAFees.FirmCode) AND
([260 Corrected FICL MF Assets].Month = TAFees.Month)

WHERE
((Not ([260 Corrected FICL MF Assets].SeriesCode)="SA") AND
(Not ([260 Corrected FICL MF Assets].SubLOB)="FRS") AND
(([260 Corrected FICL MF Assets].Month) Between "200607" And "200612"));


:

Post your SQL statement.
--
KARL DEWEY
Build a little - Test a little


:

This one is driving me nuts.

I am joining a table with a query on six fields to get to a unique row level
join. The table is the complete record set and the query is a subset with
some additional calculations. When I left join the six fields the resulting
set excludes the unmatched data which it shouldn't. I am running Access 2003
SP2.

Any help?

Thanks,

Tom.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top