Relationship confusion

[dphal]

Am building a CMS for a client who is an investment adviser and am having a problem with a particular relationship setup.

The tables are (tables in """"):

- "Investment provider" which has a one-to-many relationship with "investment products" via provider_no.

- "Client investments" which has a one-to-many relationship with "client investment products" via investor_no.

So a "client investment" can have one "investment provider" but many "investment products".

What I am trying to achieve is when a new "client investment" record is created, the user selects the "investment provider" and when they go to create the "client investment product" they are presented with a list of "investment products" provided by the "investment provider".

Cannot get it to work, and am completely stumped....brain is mush.

To put this in a real world example:

Citicorp is an "investment provider"

Citicorp Balanced Fund is an "investment product"

Citicorp Growth Fund is another "investment product"

Axa is another "investment provider"

Axa Property is an "investment product"

John Smith has a "client investment" and wants Citicorp

John Smith's "client investment products" can only be selected from Citicorp Balanced Fund or Citicorp Growth Fund.

How can I achieve this?

Thanks for the help.