many to many relations

[Susan Hoekstra]

I am defining a table and can't get it clear; See data: I have a boxtype as
primary key and product as foreign key. The problem however, is that one
boxtype might have several products in it. Or even when one boxtype has
product X in it, it contains 13 kilo/box, while when it has product Y in it,
it has 21 kilos.
I can split up tables, but it seems that I have a many - to many relation.
What is the solution?

boxtypeID kilo/box box/pallet product ID
33x27x11 4 240 mango
50x36x16 Dulce 10 84 YHD
50x40x16 Dulce 10 84 YHD
50x40x20 Dulce 16 60 watermelon
50x40x20 Marlin 16 66 watermelon
50x40x20 Negra 16 66 watermelon
50x40x20 White 16 60 watermelon
50x40x21 Cocorisa 16 60 watermelon
60x40 18 75 pineapple
60x40x16 Black 13 65 YHD
60x40x16 Dulce 13 65 YHD
60x40x16 Green 13 65 YHD
60x40x16 Royal Coast 13 50 Melon Blanco
60x40x23 Royal Coast 21 50 watermelon
60x40x23 Royal Coast 13 50 piel de sapo, YHD, Melon Blanco
bin 500 2 watermelon
El Barto 22,5 60 yuca
Thanks for answering

 

[Jeff Boyce]

Reponded to in another newsgroup. Check the newsgroup protocol info about
posting in multiple groups on the same topic...

 

[John Vinson]

I can split up tables, but it seems that I have a many - to many relation.
What is the solution?

As Jeff says, it's a bit hard to understand exactly what you're
modeling. But any time you have a many to many relationship, you need
*three tables*: the "one" side; the other "one" side; and a third
table to model how the two entities are related.

At a GUESS, you might want to have three tables: BoxTypes (with one
record per type of box); Products (what you pack in boxes); and
Packing, with a field for Boxtype, for the ProdcutID, and any other
fields you need for an individual packed box (quantity, weight, or
whatever).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps