database design

[ckloch]

I have a database which tracks client investments. Periodically we
have to recommend a change to these investments. When we recommend
changes I need to print off a form, which shows the old investments,
the ticker, and the investment category along with all of this same
information for the new investment.

I have a table that has all of the investments offered for all
clients. This table contains all of the information regarding the
investments. Right now I have a form that assigns investments to
clients. In the investment table there is an investment nubmer that
is the primary key. This is an autonumber field.

In the client table the client number is the primary key and it
contains all of the information regarding the client.

Then I have two more tables. The first table has the client number,
if the investment is actively used, and an ID, which is the primary
key.

The second table has the investment ticker, the effective date, the id
(primary key), a look up the the client nubmer from the table
mentioned above. And possibly the proposed new investment ticker.

I have tried numberous ways of setting this up. Inevitably I get to a
query and when I try to pull the new investment category and name, I
get the information from the old investment.

I have tried foreign keys but that doesn't work since the proposed new
investment is not always data in this field.

I am at wits end on designing this and am desperate for help...

 

[Guest]

The investment table and the client table look fine. I don't however
understand the other two table and what their purposes are.
Explain "ticker".
Explain the ID of the two tables.
Explain "effective date".
Can a client only have one investment at a time (or more)?
Are you suggesting that the client drop one investment and move the funds
into a single new investment?
Do you have any experience in table normalization?

If you can get me the answers to these question (and possibly others) I'll
be happy to help. You may need to send me a copy of the db at some point
(with names changed and no social security or credit card info) so that I can
better understand.

James Deckert