Small issues with table relations

[Armitage2k]

Hi!

I have a problem with a relationship between 3 tables. I am using the tables clients (with all my clients), products (with my 6 products) and investments (of each client for each (!) product) to keep track of the activities of a small investment company.
Since every client has bought a different product, I am using a subform of the products table to display the products which the customer selected, and then in another subform want to show the investments for THOSE selected products only.

the following problems occur:
1. Form: CLIENT DETAILS:
In the subform investments I have to be able to update the investments for the products which are selected in the products subform. How can I insert a dropdown menu to show only those product which the customer selected, instead of ALL available products?

2. Form: CLIENT LIST
when opening a client from the list, the Client Details form goes into a filter mode, allowing me to show information for the opened client ONLY. I want to be able to use the Next and Previous arrows to page through the clients.

3. CLIENT DETAILS
When using the Go to tab in the top left corner, it should show me the client name, email and mobile number of all clients. it shows me the email and mobile of all clients, but the client name is ALWAYS the one from the opened client. any ideas?

Thanks a lot for all the help, it is really appreciated!!
A2k

 

[MarkK]

It seems to me you have three things to worry about:
1) Clients
2) Products
3) Transactions
This appears to be a textbook many-to-many relationship between clients and products. One client might have many products and one product might be owned by many clients.
A transaction needs a date and an amount of shares, which is positive if it's a purchase and negative if it's a sale.
I'd expect tables to look about like ...

[B]tClient[/B]
ClientID (PK)
Client

[B]tProduct[/B]
ProductID (PK)
Product

[B]tTransaction[/B]
TransactionID (PK)
ClientID (FK)
ProductID (FK)
Date
Shares
PricePerShare

 

[Armitage2k]

Thanks for the reply.

Your analysis is indeed correct. However, I would like to ask, what are PK and FK indicating?

Thanks,
A2k

 

[MarkK]

Primary and Foreign Key.
Primary key is unique in the table in which it appears.
Foreign key is equal to the Primary Key in a related parent table.

 

[Armitage2k]

well, this is actually exactly how the relationships of my table are set, but I still doesnt work.
Everytime I select one or more products in the product subform for a client, the selection is stored, but when I want to enter investment data on the investment screen, it doesnt show me the selected products, instead shows all products, and once I select one, it applies it to all former and future investments.

the attached db in my first post actually shows this.
thanks a lot,

A2k

 

[MarkK]

The RecordSource of the subform you mention appears to be the table tblClientsProducts, which serves as a join only. It has no other data.
What I would expect to see is a Transaction form on the tTransaction table. In that form you select the Client from a ComboBox bound to tTransaction.ClientID and you select a Product from a Combo bound to the tTransaction.ProductID, and you complete the details of the transaction; the date, the number of shares purchased or sold, dollar values etc...
tTransaction is where the rubber hits the road. Clients and Products will rarely change.
Making sense?