Alpha DevCon 2018
Results 1 to 5 of 5

Thread: Many To Many?

  1. #1
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    638

    Default Many To Many?

    I have two .dbf tables: Orderdetail.dbf and Consultcpo.dbf

    Code:
    Orderdetail.dbf   Fields
    Ordernum   Productnum   
    2001               345
    2001               777
    2002               102
    2003               200
    2003               654
    2003               765
    etc                   etc
    ie the Orderdetail table has one record for each product on the Order

    Code:
    Consultcpo Fields
    Ordernum      Consultnum
    2001                 156
    2002                 345
    2002                 366
    2003                 156
    etc                      etc
    ie the Consultcpo table has one record for every Consultant on the Order

    I want to create a new table that allows me to associate each Consultant with all of the products on the orders

    Code:
    New Table Fields
    Ordernum        Consultnum       Prodnum
    2001                   156                  345
    2001                   156                  777
    2002                   345                  102
    2002                   366                  102
    2003                   156                  200
    2003                   156                  654
    2003                   156                  765
    etc                        etc                   etc
    I have been trying Joins and Intersections but neither works.

    How can I create an association between Consultants and Products sold?

    Thanks
    Last edited by Al Buchholz; 05-23-2017 at 10:00 AM. Reason: code formatting option keeps the columns intact

  2. #2
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,431

    Default Re: Many To Many?

    You can make a set with a one to one link from OrderDetail to Consultcpo linking the Ordernum.

    Then copy the set to the new table.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  3. #3
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    638

    Default Re: Many To Many?

    Thanks AL. ..
    But I'm not sure I understand your suggestion

    There is a one to many relationship between OrderDetail and consultcpo. . There can be many consultants on an order... they all get credit for every product.

  4. #4
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,431

    Default Re: Many To Many?

    I misread.

    Then change from one to one to one to many in the set.

    A join only gives a one to one.

    A set can do a one to many.

    join_vs_set.png
    Last edited by Al Buchholz; 05-23-2017 at 10:57 AM.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    638

    Default Re: Many To Many?

    Thanks Al
    That worked perfectly!

Posting Permissions

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