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

Thanks

 

[Smartin]

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.

Thanks

Hello,

This does not sound like good design for your data. Please correct me if
I am wrong, but it seems to me a fund could have many clients, and a
client could have many funds (perhaps even if not at one time). This
many-many relationship requires a third table to be modeled correctly:

Funds -- 1:many -- FundClient -- many:1 -- Clients

In this model you can attribute a date to a particular Fund+Client pair,
along with a recommendation, a status, and whatever else pertains to the
Fund+Client event. You can then query for the most recent, or the
"active" fund choices by client.

HTH