Many To Many?

[David Boomer]

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

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

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

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

[Al Buchholz]

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.

[David Boomer]

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.

[Al Buchholz]

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

[David Boomer]

Thanks Al
That worked perfectly!