Results 1 to 6 of 6

Thread: Family Problem?

  1. #1
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139

    Unanswered: Family Problem?

    Hello to all of you. It's been quite a long time since I posted something. But now I really need help.

    I have this mdb that's very simple. One table with a list of products and one with a list of -let's say- families. Every product belongs to one or more than one family. How can I relate that? Simply put: every product should belong to one or more family so I can query the table retreiving only products belonging to a family. A second problem should be: I would like to query the table and if a product belongs to say two families I should find it twice.

    Is that possible?

    Probably that is a simple question for you gurus out there. And sometimes I am surprised how someone can solve big problems and waste time on simpler ones - like this.

    Thanks to anyone in advance
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    How many instances of product can a single instance of family correspond to.

    Currently:
    Code:
    Family_ID,       Product_ID
    (Primary KEY)
         1                      PA
         2                      PA
         3                      PB
         4                      PB
    In my opinion, the above relation does not represent a real life scenario.

    A correct representation would likely be:

    Product_ID, Family_ID
    Primary Key (Product_ID)

    If none of the above solutions meet your requirement, then

    Product_ID, Family_ID
    Primary Key (Product_ID, Family_ID)
    Last edited by r123456; 03-03-04 at 08:13.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    The relation goes like this:
    1 product ---> many families

    eg

    Product ID Family id
    AA 1, 2, 3
    BB 3, 4, 5
    CC 1, 3, 5

    How can I achieve a many to many relation?

    I looked in the help and I tried what it says but... Creating a conjuction table is not working. It doesn't create the relations and still I can't enter more than one family per product.

    Thanks for being so fast!
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Your relationship is therefore, many to many.

    Access 2002:

    Products(product_id) Primary Key (product_id)
    Family(Family_ID) Primary Key (Family_id)

    Product_Families(Product_Id, Family_ID) Primary Key (Product_id, Family_id)
    Attached Thumbnails Attached Thumbnails image.jpg  
    Last edited by r123456; 03-03-04 at 08:27.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    Thanks a lot man!

    I'll try that.

    I've always thought that this is a great forum!

    I owe you a beer! -virtual?-
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  6. #6
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    Oh by the way the relationship is the other way round Product with product and family with family.

    Thanks again
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •