Results 1 to 2 of 2
  1. #1
    Armitage2k is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    4

    Table relationship - one relations for 2 values..?


    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

  2. #2
    Datagopherdan is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Dec 2008
    Posts
    220
    Sounds like you need to make a junction table for your transactions. It would contain a field for your client id and your product id. You would set the relationship on each one of those tables. You would use this table to record the transactions. You could create a combo-box with the product id in your junction table as the control source and create a query using the product id and product name from the products table and use it as the record source for your combo-box. Now set the combo-box to 2 columns and set the widths to 0,1. This will hide the id and only display the product name but the field itself will be assigned the product id.

    Dan
    Access Development

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-04-2010, 05:26 PM
  2. Replies: 0
    Last Post: 03-15-2010, 02:38 AM
  3. two relations to one table?
    By kannuberg in forum Forms
    Replies: 19
    Last Post: 09-27-2009, 11:25 AM
  4. Access Table - Relationship Question
    By vixtran in forum Database Design
    Replies: 5
    Last Post: 06-12-2009, 10:10 PM
  5. Table values in forms
    By xzxz in forum Access
    Replies: 3
    Last Post: 10-28-2008, 08:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums