define ID

[Susan Hoekstra]

I am defining a table with the boxtype as ID. However what do I have to do
when 1 boxtypeID might have several products in it. Or the sistuation where
one boxtype might have 16 kilos in it for one product and 21 kilo when it is
filled with the other product? I can't get it clear which tabels I have to
prepare.
primary key
boxtypeID kilo/box box/pallet productID
33x27x11 4 240 mango
50x36x16 Dulce 10 84 YHD
50x40x16 Dulce 10 84 YHD
50x40x20 Dulce 16 60 watermelon
50x40x20 Marlin 6 66 watermelon
50x40x20 Negra 16 66 watermelon
50x40x20 White 16 60 watermelon
50x40x21 Cocorisa 16 60 watermelon
60x40 14 75 pineapple
60x40x16 Black 13 65 YHD
60x40x16 Dulce 13 65 YHD, piel de Sapo, Melon Bl
60x40x16 Royal Coast 13 60 Melon Blanco
60x40x23 Royal Coast 21 50 watermelon
60x40x23 Royal Coast 13 50 piel de sapo
bin 500 2 watermelon
El Barto 22,5 60 yuca

Thanks for answering.

 

[Jeff Boyce]

Susan

I'm not sure I follow what you are trying to model. You have boxes, I got
that. You have box types ... I'm a little fuzzy on this. You have "things
you can put in a box" -- I'm lost completely here!

Could you step back from HOW you've modeled your data and just describe the
basic elements and their relationships? For example, You may wish to store
information about a 12 x 12 x 12 box, and that box can be build using 1)
heavy construction paper, 2) medium weight corrugated, 3) heavy waxed
corrugated, 4) light wooden slats, 5) ... (get the idea?). And if each
one of these can be filled with a) mushrooms, b) apples, c) watermelons,
...., you have what sounds to me like a series of one-to-many relationships.

Its the data and relationships I'm asking for, as those should guide how
your tables are designed in Access.

 

[Susan Hoekstra]

Jeff, ok I'll try to explain better. I have a lot of different labels, and in
excel I made a codebook. This worked all perfectly, bit now I want to redo
the whole system in access. So I'm making tables and in order not to do
things twice,finding the right relations between tables. The thing is I get
confused getting the right primary keys. There are 6 different products, and
12 different kind of boxtypes. and 10 different producers and 26 different
varieties etc. But I start simple: Let say watermelon can go into 2 different
boxtypes. And mango may use 3 different boxtypes. Some products share the
same boxtypes.

let say:
Boxtype: (60x40), (50x40) (20x30)
Product: ( Watemelon) (Pineapple) (mango)
Kilo/box:

watermelon might go into a 60x40 box or a 50x40 box
the mango uses 20x30 and 50x40 and 60x40
Then a mango 50x40 weighs 4 kilo and a watermelon in a 50x40 weighs 6 kilo.

How to organise the tables? Hope it is clear now?
Thanks anyway,
Susan

 

[Jeff Boyce]

Appears to have been answered in another 'group.

Jeff Boyce
<Access MVP>