Hello everyone.
I have a bit of puzzling situation here.
I created a database for a small investment company to keep track of their clients, their clients investments, and naturally the products those clients would invest in. After creating relationships between those 3 tables (clients, products, investments), I can successfully display individual information of those 3 segments for each client, only the products table is giving me troubles.
Generally, I want to be able to display the investments for the product(s) which the client chose, and then those selected products in a lookup value column together with the investment information. Since the clients are investing in their products daily, the staff will add a new line for the investments of the product(s) everyday.
Now, the problem is that I created a relationship between the products and the investments via the unique ProductID and InvestmentID, but now would like to display the ProductName column, instead of the ID. When setting up lookup value relation, it creates me a new product with a new ID, but identical ProductName, everytime i select it from the list.
Naturally this gives me troubles later when I want to create a report showing the investments for each individual product, as the report shows me all products over and over again, due to the unique ID...
I attached the Access 2007 sample database. I would highly appreciate if someone could take a look and maybe point me in the right direction.
Thanks a lot!
A2k