Returning Empty Field

[ckloch]

I have a query which includes joins and subquery. One of the fields
in the query is coming back empty. Below is all the information
from the tables involved and query.... The field that is coming
back empty is the fund name.

Thanks


I:\SHARED\WMG\Retirement Services\Procedures\Fund Holdings
Thursday,
February 15, 2007
Table: Fund Data Page: 1
Properties
DateCreated: 3/29/2002 2:46:58 PM DefaultView:
Datasheet
FrozenColumns: 3 GUID: {guid {936BD32F-2B17-
47DA-9AF0-
LastUpdated: 2/8/2007 9:50:17 AM NameMap: Long
binary data
OrderBy: [Fund Data].[Fund Name] OrderByOn: True
Orientation: Left-to-Right RecordCount: 244
Updatable: True
Columns
Name Type Size
Fund ID Long Integer 4
Fund Name Text 50
Ticker Text 50
Category Name Text 50
Morningstar Category Text 50
Revenue Sharing Yes/No 1
Perform Date Date/Time 8
3 Month Ret Text 50
Ytd Return Text 50
1 Year Ret Text 50
3 Year Ret Text 50
5 Year Ret Text 50
10 Year Ret Text 50
2006 Return Text 50
2005 Return Text 50
2004 Return Text 50
2003 Return Text 50
2002 Return Text 50
2001 Return Text 50
2000 Return Text 50
1999 Return Text 50
1998 Return Text 50
1997 Return Text 50
1996 Return Text 50
1995 Return Text 50
1994 Return Text 50
1993 Return Text 50
Risk Number Double 8
Expense Ratio Text 50
Table Indexes
Name Number of Fields
Fund DataFund ID 1
Fields:
Fund ID Ascending
Fund DataRisk Number 1


Table: Fund Data Page: 2
Fields:
Risk Number Ascending
Fund DataTICKER 1
Fields:
Ticker Ascending
Fund Name 1
Fields:
Fund Name Ascending
Risk TableFund Data 1
Fields:
Risk Number Ascending


Table: tblPlanData Page: 3
Properties
DateCreated: 1/25/2007 5:15:02 PM DefaultView:
Datasheet
GUID: {guid {AE3F3A11-9A2B- LastUpdated: 2/8/2007
11:45:19 AM
40F6-8A22-3157C84DC226}}
NameMap: Long binary data OrderByOn: False
Orientation: Left-to-Right RecordCount: 145
Updatable: True
Columns
Name Type Size
Account Number Text 50
Plan Name Text 70
Employer Name Text 50
Alpha Sort Text 50
Plan ID # Text 50
Contact Person Text 50
Salutation Text 50
Employer ID # Text 50
Max Deferral Percentage Text 50
Address 1 Text 50
Zip Text 50
Phone Number Text 50
FAX Number Text 50
Plan Number Text 50
Effective Date Date/Time 8
Deferal Deadline Memo -
Heartland Expense Text 50
Active Yes/No 1
Plan Date Date/Time 8
Annual Review Date/Time 8
Employee Meeting Date/Time 8
Relationship Manager Text 50
Account Type Text 50
Asset Fee Schedule Text 50
5500 Fee Currency 8
Safe Harbor Yes/No 1
tblFees_ID Long Integer 4
tblCompDef_ID Long Integer 4
tblAddress_ID Long Integer 4
tblYearEnds_ID Long Integer 4
Table Indexes
Name Number of Fields
NormRel1 1
Fields:
tblFees_ID Ascending
NormRel2 1


Table: tblPlanData Page: 4
Fields:
tblCompDef_ID Ascending
NormRel3 1
Fields:
tblAddress_ID Ascending
NormRel4 1
Fields:
tblYearEnds_ID Ascending
PrimaryKey 1
Fields:
Account Number Ascending


Table: tblPlanFunds Page: 5
Properties
DateCreated: 5/16/2002 10:12:26 AM DefaultView:
Datasheet
GUID: {guid {C9497C97-126B- LastUpdated: 2/12/2007
1:09:02 PM
453A-BC37-
NameMap: Long binary data OrderByOn: True
Orientation: Left-to-Right RecordCount: 1483
Updatable: True
Columns
Name Type Size
Account Number Text 50
Fund ID Long Integer 4
Trades allowed Yes/No 1
Target Retirement Yes/No 1
EffectiveDate Date/Time 8
NewFundID Long Integer 4
Table Indexes
Name Number of Fields
Fund ID 1
Fields:
Fund ID Ascending
NewFundID 1
Fields:
NewFundID Ascending
Plan ID 1
Fields:
Account Number Ascending
PrimaryKey 2
Fields:
Account Number Ascending
Fund ID Ascending
tblPlanDatatblPlanFunds 1
Fields:
Account Number Ascending


Query: qryFundChange Page: 1
Properties
DateCreated: 2/9/2007 12:09:17 AM DefaultView:
Datasheet
DOL: Long binary data GUID: {guid {231A4786-6627-
4B4C-9710-3E3BD1E7869B}}
LastUpdated: 2/13/2007 2:03:39 PM MaxRecords: 0
ODBCTimeout: 60 OrderByOn: True
Orientation: Left-to-Right RecordLocks: No Locks
RecordsAffected: 0 RecordsetType: Dynaset
ReturnsRecords: True Type: 0
Updatable: True
SQL
SELECT tblPlanData.[Account Number], [Fund Data].FundID AS
OldFundID,
[Fund Data].[Morningstar
Category] AS OldFundCat, [Fund Data].[Fund Name] AS [Old
FundName],
[Fund Data].Ticker AS
OldFundTicker, [Fund Data_1].FundName AS Expr1, [Fund
Data_1].
[Morningstar Category], [Fund
Data_1].Ticker
FROM tblPlanData RIGHT JOIN ((tblPlanFunds LEFT JOIN [Fund
Data] ON
tblPlanFunds.[Fund
ID]=[Fund Data].[Fund ID]) LEFT JOIN [Fund Data] AS [Fund
Data_1] ON
tblPlanFunds.NewFundID=[Fund Data_1].[Fund ID]) ON
tblPlanData.
[Account
Query Parameters
Name Type
[Fund Data].FundID Text
[Fund Data_1].FundName Text
Columns
Name Type Size
Account Number Text 50
OldFundID Binary 2818292
OldFundCat Text 50
Old FundName Text 50
OldFundTicker Text 50
Expr1 Binary 2818292
Morningstar Category Text 50
Ticker Text 50
Table Indexes
Name Number of Fields
NormRel1 1
NormRel2 1
NormRel3 1
NormRel4 1
PrimaryKey 1


Query: qryFundChange Page: 2
Fund DataFund ID 1
Fund DataRisk Number 1
Fund DataTICKER 1
Fund Name 1
Risk TableFund Data 1