Please read data and suggest how to make table

[silvertabb]

Assume my data is as follows:



finished product lot 1 used raw material lots A, B, C
finished product lot 2 used raw material lots A
finished product lot 3 used raw material lots B,C,D
finished product lot 4 used raw material lots B,A
and so forth.


As you can see, the same raw material was shared for some finished product lots. I need to make set up some table, but I am having difficulty because of the following issues:

* finished product lots in most cases are made from more than one raw material lot.
* same raw materials used to make various finished product lots.

I just reviewed the relationship topic. I will be able to apply it once I know to set my tables.

you help is appreciated.

 

[Norie]

As andrew93 stated in one of your other posts this is a many to many relationship.

So you need 3 tables - Products, RawMaterials and UsesRawMaterials.

The third table UsesRawMaterials will be used to represent the relationship between products and raw materials.

 

[silvertabb]

Third table

The third table (uses of raw material) is scary. In a sense that i will have to have one table for each lot of raw material. For example, I will have a table for raw material lot A which describes its use- how many finished products lots are associated with lot A. I am correct?

 

[Norie]

I don't follow.

Could you actually post some example data?

 

[silvertabb]

Data

Here is some data:

Finished Lot 1- used RM lots A, B, C
Finished Lot 2- used RM lots B
Finished Lot 3- used RM lots C, D, E, F
Finished Lot 4- used RM lots B, F
Finished Lot 5- used RM lots A, B, C
Finished Lot 6- used RM lots A, B, C, F
.
.
.
Finished Lot 100- used RM lots X, Y, A


I have the list of finished lots, RM lots. Do I need to, for example, make a separate table for RM lot A and in it capture that it is associated with Finished Lots 1, 5, and 6. If true, then I will make a separate table for each RM lots B, C, D, E, F, ...., X, Y, Z.

 

[Norie]

What is RM lots?

Can you actually post what tables you currently have?

 

[Andrew Fergus]

Apologies for butting in. You wrote this earlier :

The third table (uses of raw material) is scary. In a sense that i will have to have one table for each lot of raw material

At first sight many-to-many relationships can seem overwhelming but they aren't that bad. You don't need to have a new table for each RM lot (Raw Material lot?). The third table would give you all of the bits almost exactly as you described it in your example. Instead of each lot being on one line, the data is held in say 3 lines in the table (where one lot has 3 RM lots). You would use the third table for every lot and every RM lot - that's the beauty of it, you have all of the data in one place without duplicating the lot or RM descriptions etc.

For example, Lot 1 would appear in the third table like this :

Lot #1, RM A, qty = say 1 (I'm guessing the Lot 1 only used 1 of RM A)
Lot #1, RM B, qty = say 20
Lot #1, RM C, qty = say 5

In the same table, Lot 100 would look like this :

Lot #100, RM A, qty = say 10
Lot #100, RM X, qty = say 1
Lot #100, RM Y, qty = say 12

....and so on....

Sorry if I didn't explain it very well in your other thread.

Andrew

 

[silvertabb]

primary key

Andrew,
thanks for your reply.

What would be the primary key in table 3? Finished product lot appear in more than one row under the same field and RM is the same.

 

[Andrew Fergus]

Hi
Add a another field, an autonumber field (just to provide a unique identifier, which may or may not be useful later) and make that your key. You won't need to show this field on any forms or reports but as I said you may regret it later if you don't have this.
HTH, Andrew.