Relationship help

[ckloch]

I have a two tables. the first table has a list of all mutual funds
for all clients and the recommended replacement along with the
effective date. The second table the the mutual fund data table.
my relationship is a one to many from the mutual fund data table to
the old mutual fund for each client. I ultimately want to get data
(ie fund category and ticker) for the recommended new fund to a
report. I am not sure if an update query to a new table is the best
way to handle this.... If so how do I get the query to pull the
information for the new fund and not the old fund.

Table one
Old fund
Client Number
Effective Date
New Fund


Table Two
Fund Number
Fund Name
Fund Ticker
Fund Category
Performance Date
Performance numbers


Table three
Client Number
Client Name
Client Address
etc


There is a 1 to many relationship between table two with fund number
and table one old fund. There is a one to many relationship between
table three client nubmer and table one client number. I need a
report that shows me old fund name old fund category old fund ticker
new fund name new fund category and new fund ticker..... I have a
form set up to enter this data but now i need help getting it into a
report.

 

[Joseph Meehan]

I have a two tables. the first table has a list of all mutual funds
for all clients and the recommended replacement along with the
effective date. The second table the the mutual fund data table.
my relationship is a one to many from the mutual fund data table to
the old mutual fund for each client. I ultimately want to get data
(ie fund category and ticker) for the recommended new fund to a
report. I am not sure if an update query to a new table is the best
way to handle this.... If so how do I get the query to pull the
information for the new fund and not the old fund.

Table one
Old fund
Client Number
Effective Date
New Fund


Table Two
Fund Number
Fund Name
Fund Ticker
Fund Category
Performance Date
Performance numbers


Table three
Client Number
Client Name
Client Address
etc


There is a 1 to many relationship between table two with fund number
and table one old fund. There is a one to many relationship between
table three client nubmer and table one client number. I need a
report that shows me old fund name old fund category old fund ticker
new fund name new fund category and new fund ticker..... I have a
form set up to enter this data but now i need help getting it into a
report.

I am getting a little lost. What is the relationship between old funds
and new funds? Do old funds become new funds somehow?

 

[ckloch]

Old funds are replaced with a new fund.

 

[Guest]

Here's a query that will get you the report of old and new funds for each
client. I renamed your tables table1 = Client_Fund; table2 = Funds; table 3 =
Clients
Note that Funds is joined to Client_fund twice.

SELECT Clients.ClientName, Funds.FundName AS OldFundName, Funds.FundCategory
AS OldFundCat, Funds.FundTicker AS OldFundTick, Funds_1.FundName,
Funds_1.FundCategory, Funds_1.FundTicker
FROM ((Clients
RIGHT JOIN Client_Fund ON Clients.ClientName = Client_Fund.ClientNumber)
LEFT JOIN Funds ON Client_Fund.OldFund = Funds.FundNumber)
LEFT JOIN Funds AS Funds_1 ON Client_Fund.NewFund = Funds_1.FundNumber;

 

[ckloch]

I am getting this message.

Syntax error (missing operator) in query expression
'Clients.ClientName'.

 

[Guest]

You may need to adjust the syntax of the SQL to fit your exact table and
field names.

 

[ckloch]

I have the syntax correct for the most part. but when i run the
query I get the message "Type mismatch expression." What does that
mean?

 

[John W. Vinson]

I have the syntax correct for the most part. but when i run the
query I get the message "Type mismatch expression." What does that
mean?

It means that you have a type mismatch in some expression: e.g. you're
trying to join a text field to a number field, or the like.

This can happen if you have Lookup Fields in your table. If you try to
create a query joining the lookup field to another table containing
the text value that you SEE in the lookup field, you will get this
error, because what is actually IN your table is a concealed numeric
ID.

If you would like help debugging the query, please post the SQL - we
can't see it from here.

John W. Vinson [MVP]

 

[Guest]

Sorry,
had the join going to clients.name instead of clients.number

SELECT Clients.ClientName, Funds.FundName AS OldFundName, Funds.FundCategory
AS OldFundCat, Funds.FundTicker AS OldFundTick, Funds_1.FundName,
Funds_1.FundCategory, Funds_1.FundTicker
FROM ((Client_Fund
LEFT JOIN Funds ON Client_Fund.OldFund = Funds.FundNumber)
LEFT JOIN Funds AS Funds_1 ON Client_Fund.NewFund = Funds_1.FundNumber) LEFT
JOIN Clients ON Client_Fund.ClientNumber = Clients.ClientNumber;

 

[ckloch]

That is ok, I got it figured out on my own..... YEAH. But now the
only thing I can figure out is it does not pull the investment name.
It pulls the correct ticker and category, but not the name.

Below is the sql.. What is wrong?

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 Number] = tblPlanFunds.[Account Number];

 

[John W. Vinson]

That is ok, I got it figured out on my own..... YEAH. But now the
only thing I can figure out is it does not pull the investment name.
It pulls the correct ticker and category, but not the name.

Below is the sql.. What is wrong?

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 Number] = tblPlanFunds.[Account Number];

It presumably does not include the field, or perhaps the table and
field, which contains the investment name.

Since I cannot see your database I have no way to know what field or
table that might be, other than to suggest that you might have a
lookup table (and perhaps, if you were tricked into using the
misfeature, a Lookup Field) which gives the investment name based on
the ticker.

Just include this table in your query, twice, joining one to [Fund
Data].[Ticker] and the other to [Fund Data_1].Ticker.

John W. Vinson [MVP]

 

[ckloch]

Since I cannot see your database I have no way to know what field or
table that might be, other than to suggest that you might have a
lookup table (and perhaps, if you were tricked into using the
misfeature, a Lookup Field) which gives the investment name based on
the ticker.

Just include this table in your query, twice, joining one to [Fund
Data].[Ticker] and the other to [Fund Data_1].Ticker.

John W. Vinson [MVP]

I am not sure what this means.... what do you mean i have a lookup
table? Do you mean lookup field.... I do not have look up fields
in the table.....

I am not sure how to include the table in the query twice. Which
table?

How do I post screen shots on the web for others to view.

 

[John W. Vinson]

I am not sure what this means.... what do you mean i have a lookup
table? Do you mean lookup field.... I do not have look up fields
in the table.....

Good. But do you have a table which has a single record for each
Ticker value, with the stock name for that ticker? If so, you can use
it as a lookup table to look up the stock name by joining it to the
ticker.

I am not sure how to include the table in the query twice. Which
table?

The table (if there is a table - if there isn't perhaps you need to
create one!) which translates tickers to stock names. To add it twice,
use the Add Tables tool and select that table name; then select it
AGAIN - Access wil give the second copy of the table an alias name by
appending the number 1 to the table name.

How do I post screen shots on the web for others to view.

Irrelevant, since most of us won't risk going to unvouched websites or
downloading graphics anyway; and unnecessary, since you can post the
names of your tables and their key fields in text messages.

John W. Vinson [MVP]

 

[ckloch]

The Fund Data table which includes both the ticker and the name is
already in the query twice..... I joined ticker for both tables and
when i try to run the query I get this message.....

The SQL statement could not be executed because it contains ambiguous
outer joins. To force one of the joins to be performed first, creat
a separate query that performs the first join and then include that
query in your SQL statement.

How do I fix this?

 

[John W. Vinson]

The SQL statement could not be executed because it contains ambiguous
outer joins. To force one of the joins to be performed first, creat
a separate query that performs the first join and then include that
query in your SQL statement.

How do I fix this?

As it suggests. Create a separate query that performs the first join -
save it under some name - and then include that query.

If you'ld like help doing so please post the SQL view of the query.

John W. Vinson [MVP]

 

[ckloch]

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,
subqryFundChange.[Fund Name], [Fund Data_1].FundName AS Expr1, [Fund
Data_1].[Morningstar Category], [Fund Data_1].Ticker
FROM subqryFundChange INNER JOIN (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 Number] = tblPlanFunds.[Account Number]) ON subqryFundChange.
[Fund Name] = [Fund Data_1].[Fund Name];

 

[ckloch]

Does anyone have any suggestions as to why this SQL will not pull the
Fund name?