Query 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.

 

[Dale Fye]

I assume you want the client name as well, and that the FundNumber in Table 2
is the PK to OldFund and NewFund FK columns in Table 1. This should give you
the old fund name as well as the new fund name, ticker, and categories. If
you really don't care about the old funds name, you can remove any of the
lines that refer to T2a.

SELECT T3.ClientName,
T3.ClientNumber,
T1.OldFund,
T2a.OldFundName,
T1.EffectiveDate,
T1.NewFund,
T2b.NewFundName,
T2b.FundTicker,
T2b.FundCategory
FROM [Table three] T3
INNER JOIN [Table 1] T1 ON T3.ClientNumber = T1.ClientNUmber
INNER JOIN [Table 2] T2a ON T1.OldFund = T2a.FundNumber
INNER JOIN [Table 2] T2b ON T1.NewFund = T2b.FundNumber
ORDER BY T1.ClientNumber

HTH
Dale

 

[ckloch]

I assume this is a select query and the inner joins are automatic in
the query or do they need to be set up?

 

[Dale Fye]

You should be able to cut and past directly into the SQL view.

 

[ckloch]

I am still one little bit confused. for the T2a and T2b... what
are you referring to?

 

[Dale Fye]

T1, T2a, T2b, and T3 are all aliases for the table names (saves space and
generally makes it easier to use). T2a and T2b are two different versions
of your Table 2. You need two versions if you want to get both the
OldFundName and the NewFundName.

If you look in the FROM clause, you will see that I have included joins
between Table 1 (T1) and Table 2 (T2a) on the OldFundNum and the FundNum and
then have a separate join between T1 and T2b on the NewFundNum and FundNum.

HTH
Dale