Relationship problem

Allen,
Thanks a lot for your help.
I'm pretty sure I should be able to get around from here.
Cheers,
Graeme

"Allen Browne" wrote:


The basic idea is that a product is made from other products, so you have
tables like this:

Product table:
ProductID primary key
ProductName Text

ProductInProduct table:
TargetProductID foreign key
SourceProductID foreign key
Quantity number
UnitID foreign key

So the records in the ProductInProduct table look like this:
TargetProduct SourceProduct
========== ============
1 (Toffee pudding) 2 (toffee pudding base)
1 (Toffee pudding) 3 (toffee pudding sauce)
1 (Toffee pudding) 4 (toffee pudding icing)
2 (Toffee pud. base) 5 brown sugar
2 (Toffee pud. base) 6 butter
3 (Toffee pud. sauce) 7 water
3 (Toffee pud. sauce) 8 thickening
4 ...

A raw ingredient has an entry in the Product table only (not in
ProductInProduct.)

An intermediate product (such as a sauce) has an entry in the Product table,
and multiple entries in the ProductInProduct table (one for each of its
ingredients.)

An end product has an entry in the Product table, and multiple entries in
the ProductInProduct table (one for each ingredient, and these ingredients
may themselves be interemediate products.)

The recipes are generic instructions of how to make each product.
The batches are specific instances of the recipe being used (with a
created-date and a use-by date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Graeme at Raptup" wrote in
message
...
Hi Allen,
I'm coming round to the BOM concept, but don't know how to link to batches
or Raw Material Inspection.
I was going to have a table 'Recipe' that has many 'Mix'. The 'RM
Inspection' (many) relates to (one) 'Ingredient'. My thinking was that the
'Ingredient' is linked to the 'Mix' rather than the 'Recipe'. However I
then
get stuck assigning quantities to ingredients. Which brings me back to the
BOM..!
Would it be ok to build my other tables around the BOM? I'm thinking of
taking the BOM and changing the names (e.g. Components to Ingredients).
Clearly I'd have to go into the VB and change these.
Any suggestions on how to apply the BOM to my db?

"Allen Browne" wrote:

Ultimately, it's up to you as to whether the BOM approach suits your data
best, but the core idea is that everything you inspect is in the one
table:
raw ingredients, mixes, mixes that use other mixes, and end products.

The advantage of the BOM structure is the flexibility it provides. If you
don't do it this way, how will you model mixes that use other mixes? On
in
your Inspection table, how will you have a foreign key field that can
relate
to end products, raw ingredients, and all levels of mixes in between?
Even
in your Recipe table, you need a foreign key field that could be a raw
ingredient, or could be a mix; do you see that if you have separate
tables
for Ingredient and Mix, your key field cannot connect to both?

Another core concept (separate from the BOM idea) is the difference
between
a mix (a list of ingredients and quantities, like a sub-recipe) and a
batch
(a specific instance of a mix). A recipe must be able to refer to a mix.
A
batch is made from the list in the mix, on a particular date, and goes
bad
if not used. The mix is just the concoction you use to make your batches.

Hope that's helping you work through the way your data connects.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Graeme at Raptup" wrote in
message
...
Ok Allen,
I downloaded and went through that BOM sample.
I recognise that it works on a Boolean loop but I am a bit thrown by
the
VB
detail.
I don't understand how the Mix (Assembly) becomes my batch. The batch
needs
to link to various other tables.
Neither do I understand where the raw material inspection comes in.
Note that the raw material inspection is a different process to the
finished
product evaluation.
I can't help but think I have not got the normalisation right!

"Allen Browne" wrote:

Okay a key sticking point is the relation between an inspection and
the
other tables.

It seems you inspect at several points, so one of the things you might
want
to consider is a bill-of-materials type structu
http://www.mvps.org/access/modules/mdl0027.htm

With this structure, an ingredient is a "product".
The ingrediant goes into the mix, and the resultant batch is also a
"product."
The next mix is made from products: some raw ingredients, some mixes,
but
since both are in the Product table, that's as very easy relationship.

So, ultimately you inspect products. With the BOM structure, the
product
being inspected can be a raw ingredient, end product, or any level of
intermediate mix.

BOM is not overly easy to work with, but sometime it is the best
structure.

Hi Allen,
please bear with me - I'm sure this is frustrating for you.
I get the concept and can see how the ingredient can be a raw ingredient or
an intermediate product.

I am a bit thrown by the lack of relationship links between the tables, so I
assume the (BOM) VB script handles this. I also don't know what the 'Form1'
function is, is it necessary?

Your tables look the same as those in BOM, Product (Component) and
ProductinProduct (Assembly). Fine. Is the UnitID a replacement of
'AssemblyBoolean'. Does it indicate a final product?

Your tables read;
Product (ProductID, ProductName)
ProductinProduct (TargetProductID, SourceProductID, Quantity, UnitID)

What happens to the Output table?
Does the recipe form a table at all? If so does it link with ingredients?
Would the batch look something like this?
Batch (BatchID, RecipeID, CreateDate, UseByDate)

Sorry about my confusion, I'm just having difficulty finding how to link the
BOM to the rest of the database.

Cheers,

Graeme



"Graeme at Raptup" wrote:


Allen,
Thanks a lot for your help.
I'm pretty sure I should be able to get around from here.
Cheers,
Graeme

"Allen Browne" wrote:


The basic idea is that a product is made from other products, so you have
tables like this:

Product table:
ProductID primary key
ProductName Text

ProductInProduct table:
TargetProductID foreign key
SourceProductID foreign key
Quantity number
UnitID foreign key

So the records in the ProductInProduct table look like this:
TargetProduct SourceProduct
========== ============
1 (Toffee pudding) 2 (toffee pudding base)
1 (Toffee pudding) 3 (toffee pudding sauce)
1 (Toffee pudding) 4 (toffee pudding icing)
2 (Toffee pud. base) 5 brown sugar
2 (Toffee pud. base) 6 butter
3 (Toffee pud. sauce) 7 water
3 (Toffee pud. sauce) 8 thickening
4 ...

A raw ingredient has an entry in the Product table only (not in
ProductInProduct.)

An intermediate product (such as a sauce) has an entry in the Product table,
and multiple entries in the ProductInProduct table (one for each of its
ingredients.)

An end product has an entry in the Product table, and multiple entries in
the ProductInProduct table (one for each ingredient, and these ingredients
may themselves be interemediate products.)

The recipes are generic instructions of how to make each product.
The batches are specific instances of the recipe being used (with a
created-date and a use-by date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Graeme at Raptup" wrote in
message
...
Hi Allen,
I'm coming round to the BOM concept, but don't know how to link to batches
or Raw Material Inspection.
I was going to have a table 'Recipe' that has many 'Mix'. The 'RM
Inspection' (many) relates to (one) 'Ingredient'. My thinking was that the
'Ingredient' is linked to the 'Mix' rather than the 'Recipe'. However I
then
get stuck assigning quantities to ingredients. Which brings me back to the
BOM..!
Would it be ok to build my other tables around the BOM? I'm thinking of
taking the BOM and changing the names (e.g. Components to Ingredients).
Clearly I'd have to go into the VB and change these.
Any suggestions on how to apply the BOM to my db?

"Allen Browne" wrote:

Ultimately, it's up to you as to whether the BOM approach suits your data
best, but the core idea is that everything you inspect is in the one
table:
raw ingredients, mixes, mixes that use other mixes, and end products.

The advantage of the BOM structure is the flexibility it provides. If you
don't do it this way, how will you model mixes that use other mixes? On
in
your Inspection table, how will you have a foreign key field that can
relate
to end products, raw ingredients, and all levels of mixes in between?
Even
in your Recipe table, you need a foreign key field that could be a raw
ingredient, or could be a mix; do you see that if you have separate
tables
for Ingredient and Mix, your key field cannot connect to both?

Another core concept (separate from the BOM idea) is the difference
between
a mix (a list of ingredients and quantities, like a sub-recipe) and a
batch
(a specific instance of a mix). A recipe must be able to refer to a mix.
A
batch is made from the list in the mix, on a particular date, and goes
bad
if not used. The mix is just the concoction you use to make your batches.

Hope that's helping you work through the way your data connects.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Graeme at Raptup" wrote in
message
...
Ok Allen,
I downloaded and went through that BOM sample.
I recognise that it works on a Boolean loop but I am a bit thrown by
the
VB
detail.
I don't understand how the Mix (Assembly) becomes my batch. The batch
needs
to link to various other tables.
Neither do I understand where the raw material inspection comes in.
Note that the raw material inspection is a different process to the
finished
product evaluation.
I can't help but think I have not got the normalisation right!

"Allen Browne" wrote:

Okay a key sticking point is the relation between an inspection and
the
other tables.

It seems you inspect at several points, so one of the things you might
want
to consider is a bill-of-materials type structu
http://www.mvps.org/access/modules/mdl0027.htm

With this structure, an ingredient is a "product".
The ingrediant goes into the mix, and the resultant batch is also a
"product."
The next mix is made from products: some raw ingredients, some mixes,
but
since both are in the Product table, that's as very easy relationship.

So, ultimately you inspect products. With the BOM structure, the
product
being inspected can be a raw ingredient, end product, or any level of
intermediate mix.

BOM is not overly easy to work with, but sometime it is the best
structure.

Replies in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Graeme at Raptup" wrote in
message
news
Hi Allen,
please bear with me - I'm sure this is frustrating for you.
I get the concept and can see how the ingredient can be a raw ingredient
or
an intermediate product.

I am a bit thrown by the lack of relationship links between the tables,

You do create relationships between the tables I suggested.

Add a 2nd copy of the Product table to the Relationships window.
Access will alias it as Product_1.
You then create a relation from:
Product.ProductID = ProductInProduct.TargetProductID
and another relation from
Product_1.ProductID = ProductInProduct.SourceProductID

so I assume the (BOM) VB script handles this. I also don't know what
the 'Form1' function is, is it necessary?

Presumably this if from the example, which I haven't looked at for some
years.

Your tables look the same as those in BOM, Product (Component) and
ProductinProduct (Assembly). Fine. Is the UnitID a replacement of
'AssemblyBoolean'. Does it indicate a final product?

I'm guessing that in a recipe, some ingredients are measured in grams, some
in mililitres, some in cups, etc. If so, you will have a UnitOfMeasurement
table containing all the valid measurement types. The UnitID field is meant
to be a foreign key to that table. The Quantity field is just a Number (size
Double.) So the combination of Quantity + UnitID can say things like:
2 cups
500 grams


Your tables read;
Product (ProductID, ProductName)
ProductinProduct (TargetProductID, SourceProductID, Quantity, UnitID)

What happens to the Output table?
Does the recipe form a table at all? If so does it link with ingredients?

Assuming your factory has only one recipe to make any product, the
ProductInProduct table gives you the ingredients for the recipe. You might
want another related table that contains a memo field for spelling out the
steps of the recipe, but you already have the ingredients listed.

Would the batch look something like this?
Batch (BatchID, RecipeID, CreateDate, UseByDate)

Yes, that's the idea. If there is only one recipe for any product you make,
then you might be able to use ProductID instead of RecipeID. If a batch
might be double-quantity, you might want another Factor field (type Number,
size Double.)

Sorry about my confusion, I'm just having difficulty finding how to link
the
BOM to the rest of the database.

This structure is likely to be quite different from whatever you already
have in mind for the rest of the database.


Cheers,

Graeme



"Graeme at Raptup" wrote:


Allen,
Thanks a lot for your help.
I'm pretty sure I should be able to get around from here.
Cheers,
Graeme

"Allen Browne" wrote:


The basic idea is that a product is made from other products, so you
have
tables like this:

Product table:
ProductID primary key
ProductName Text

ProductInProduct table:
TargetProductID foreign key
SourceProductID foreign key
Quantity number
UnitID foreign key

So the records in the ProductInProduct table look like this:
TargetProduct SourceProduct
========== ============
1 (Toffee pudding) 2 (toffee pudding base)
1 (Toffee pudding) 3 (toffee pudding sauce)
1 (Toffee pudding) 4 (toffee pudding icing)
2 (Toffee pud. base) 5 brown sugar
2 (Toffee pud. base) 6 butter
3 (Toffee pud. sauce) 7 water
3 (Toffee pud. sauce) 8 thickening
4 ...

A raw ingredient has an entry in the Product table only (not in
ProductInProduct.)

An intermediate product (such as a sauce) has an entry in the Product
table,
and multiple entries in the ProductInProduct table (one for each of its
ingredients.)

An end product has an entry in the Product table, and multiple entries
in
the ProductInProduct table (one for each ingredient, and these
ingredients
may themselves be interemediate products.)

The recipes are generic instructions of how to make each product.
The batches are specific instances of the recipe being used (with a
created-date and a use-by date.)

"Graeme at Raptup" wrote in
message
...
Hi Allen,
I'm coming round to the BOM concept, but don't know how to link to
batches
or Raw Material Inspection.
I was going to have a table 'Recipe' that has many 'Mix'. The 'RM
Inspection' (many) relates to (one) 'Ingredient'. My thinking was
that the
'Ingredient' is linked to the 'Mix' rather than the 'Recipe'. However
I
then
get stuck assigning quantities to ingredients. Which brings me back
to the
BOM..!
Would it be ok to build my other tables around the BOM? I'm thinking
of
taking the BOM and changing the names (e.g. Components to
Ingredients).
Clearly I'd have to go into the VB and change these.
Any suggestions on how to apply the BOM to my db?

"Allen Browne" wrote:

Ultimately, it's up to you as to whether the BOM approach suits your
data
best, but the core idea is that everything you inspect is in the one
table:
raw ingredients, mixes, mixes that use other mixes, and end
products.

The advantage of the BOM structure is the flexibility it provides.
If you
don't do it this way, how will you model mixes that use other mixes?
On
in
your Inspection table, how will you have a foreign key field that
can
relate
to end products, raw ingredients, and all levels of mixes in
between?
Even
in your Recipe table, you need a foreign key field that could be a
raw
ingredient, or could be a mix; do you see that if you have separate
tables
for Ingredient and Mix, your key field cannot connect to both?

Another core concept (separate from the BOM idea) is the difference
between
a mix (a list of ingredients and quantities, like a sub-recipe) and
a
batch
(a specific instance of a mix). A recipe must be able to refer to a
mix.
A
batch is made from the list in the mix, on a particular date, and
goes
bad
if not used. The mix is just the concoction you use to make your
batches.

Hope that's helping you work through the way your data connects.

"Graeme at Raptup" wrote
in
message
...
Ok Allen,
I downloaded and went through that BOM sample.
I recognise that it works on a Boolean loop but I am a bit thrown
by
the
VB
detail.
I don't understand how the Mix (Assembly) becomes my batch. The
batch
needs
to link to various other tables.
Neither do I understand where the raw material inspection comes
in.
Note that the raw material inspection is a different process to
the
finished
product evaluation.
I can't help but think I have not got the normalisation right!

"Allen Browne" wrote:

Okay a key sticking point is the relation between an inspection
and
the
other tables.

It seems you inspect at several points, so one of the things you
might
want
to consider is a bill-of-materials type structu
http://www.mvps.org/access/modules/mdl0027.htm

With this structure, an ingredient is a "product".
The ingrediant goes into the mix, and the resultant batch is also
a
"product."
The next mix is made from products: some raw ingredients, some
mixes,
but
since both are in the Product table, that's as very easy
relationship.

So, ultimately you inspect products. With the BOM structure, the
product
being inspected can be a raw ingredient, end product, or any
level of
intermediate mix.

BOM is not overly easy to work with, but sometime it is the best
structure.

Allen,
it's working like a charm.
THANK YOU.

FYI I have added two fields to the Product table (Yes/No) for Recipe and Mix.
That way I have created a query to list only Recipes & Mixes for user forms.
Make sense?

Also, I have created a form (Product query as above) with a subform
(ProductInProduct). I have inserted a lookup field for ProductID &
ProductName that inserts data into SourceProductID. It seems to work but is
it correct?

One more question. The memo field spelling out the steps (mix method). Why
can't I just add a field to Product.ProductID? This field is only relevant to
Mixes.

Finally, the Raw Material Inspection. As each ingredient (product) can be
inspected many times I assume that Product.ProductID is linked to a table
RawMaterialInspection (with ProductID as a foreign key)?
Thanks again,
Graeme

"Allen Browne" wrote:

Replies in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Graeme at Raptup" wrote in
message
news
Hi Allen,
please bear with me - I'm sure this is frustrating for you.
I get the concept and can see how the ingredient can be a raw ingredient
or
an intermediate product.

I am a bit thrown by the lack of relationship links between the tables,

You do create relationships between the tables I suggested.

Add a 2nd copy of the Product table to the Relationships window.
Access will alias it as Product_1.
You then create a relation from:
Product.ProductID = ProductInProduct.TargetProductID
and another relation from
Product_1.ProductID = ProductInProduct.SourceProductID

so I assume the (BOM) VB script handles this. I also don't know what
the 'Form1' function is, is it necessary?

Presumably this if from the example, which I haven't looked at for some
years.

Your tables look the same as those in BOM, Product (Component) and
ProductinProduct (Assembly). Fine. Is the UnitID a replacement of
'AssemblyBoolean'. Does it indicate a final product?

I'm guessing that in a recipe, some ingredients are measured in grams, some
in mililitres, some in cups, etc. If so, you will have a UnitOfMeasurement
table containing all the valid measurement types. The UnitID field is meant
to be a foreign key to that table. The Quantity field is just a Number (size
Double.) So the combination of Quantity + UnitID can say things like:
2 cups
500 grams


Your tables read;
Product (ProductID, ProductName)
ProductinProduct (TargetProductID, SourceProductID, Quantity, UnitID)

What happens to the Output table?
Does the recipe form a table at all? If so does it link with ingredients?

Assuming your factory has only one recipe to make any product, the
ProductInProduct table gives you the ingredients for the recipe. You might
want another related table that contains a memo field for spelling out the
steps of the recipe, but you already have the ingredients listed.

Would the batch look something like this?
Batch (BatchID, RecipeID, CreateDate, UseByDate)

Yes, that's the idea. If there is only one recipe for any product you make,
then you might be able to use ProductID instead of RecipeID. If a batch
might be double-quantity, you might want another Factor field (type Number,
size Double.)

Sorry about my confusion, I'm just having difficulty finding how to link
the
BOM to the rest of the database.

This structure is likely to be quite different from whatever you already
have in mind for the rest of the database.


Cheers,

Graeme



"Graeme at Raptup" wrote:


Allen,
Thanks a lot for your help.
I'm pretty sure I should be able to get around from here.
Cheers,
Graeme

"Allen Browne" wrote:


The basic idea is that a product is made from other products, so you
have
tables like this:

Product table:
ProductID primary key
ProductName Text

ProductInProduct table:
TargetProductID foreign key
SourceProductID foreign key
Quantity number
UnitID foreign key

So the records in the ProductInProduct table look like this:
TargetProduct SourceProduct
========== ============
1 (Toffee pudding) 2 (toffee pudding base)
1 (Toffee pudding) 3 (toffee pudding sauce)
1 (Toffee pudding) 4 (toffee pudding icing)
2 (Toffee pud. base) 5 brown sugar
2 (Toffee pud. base) 6 butter
3 (Toffee pud. sauce) 7 water
3 (Toffee pud. sauce) 8 thickening
4 ...

A raw ingredient has an entry in the Product table only (not in
ProductInProduct.)

An intermediate product (such as a sauce) has an entry in the Product
table,
and multiple entries in the ProductInProduct table (one for each of its
ingredients.)

An end product has an entry in the Product table, and multiple entries
in
the ProductInProduct table (one for each ingredient, and these
ingredients
may themselves be interemediate products.)

The recipes are generic instructions of how to make each product.
The batches are specific instances of the recipe being used (with a
created-date and a use-by date.)

"Graeme at Raptup" wrote in
message
...
Hi Allen,
I'm coming round to the BOM concept, but don't know how to link to
batches
or Raw Material Inspection.
I was going to have a table 'Recipe' that has many 'Mix'. The 'RM
Inspection' (many) relates to (one) 'Ingredient'. My thinking was
that the
'Ingredient' is linked to the 'Mix' rather than the 'Recipe'. However
I
then
get stuck assigning quantities to ingredients. Which brings me back
to the
BOM..!
Would it be ok to build my other tables around the BOM? I'm thinking
of
taking the BOM and changing the names (e.g. Components to
Ingredients).
Clearly I'd have to go into the VB and change these.
Any suggestions on how to apply the BOM to my db?

"Allen Browne" wrote:

Ultimately, it's up to you as to whether the BOM approach suits your
data
best, but the core idea is that everything you inspect is in the one
table:
raw ingredients, mixes, mixes that use other mixes, and end
products.

The advantage of the BOM structure is the flexibility it provides.
If you
don't do it this way, how will you model mixes that use other mixes?
On
in
your Inspection table, how will you have a foreign key field that
can
relate
to end products, raw ingredients, and all levels of mixes in
between?
Even
in your Recipe table, you need a foreign key field that could be a
raw
ingredient, or could be a mix; do you see that if you have separate
tables
for Ingredient and Mix, your key field cannot connect to both?

Another core concept (separate from the BOM idea) is the difference
between
a mix (a list of ingredients and quantities, like a sub-recipe) and
a
batch
(a specific instance of a mix). A recipe must be able to refer to a
mix.
A
batch is made from the list in the mix, on a particular date, and
goes
bad
if not used. The mix is just the concoction you use to make your
batches.

Hope that's helping you work through the way your data connects.

"Graeme at Raptup" wrote
in
message
...
Ok Allen,
I downloaded and went through that BOM sample.
I recognise that it works on a Boolean loop but I am a bit thrown
by
the
VB
detail.
I don't understand how the Mix (Assembly) becomes my batch. The
batch
needs
to link to various other tables.
Neither do I understand where the raw material inspection comes
in.
Note that the raw material inspection is a different process to
the
finished
product evaluation.
I can't help but think I have not got the normalisation right!

"Allen Browne" wrote:

Okay a key sticking point is the relation between an inspection
and
the
other tables.

It seems you inspect at several points, so one of the things you
might
want
to consider is a bill-of-materials type structu
http://www.mvps.org/access/modules/mdl0027.htm

With this structure, an ingredient is a "product".
The ingrediant goes into the mix, and the resultant batch is also
a
"product."
The next mix is made from products: some raw ingredients, some
mixes,
but
since both are in the Product table, that's as very easy
relationship.

So, ultimately you inspect products. With the BOM structure, the
product
being inspected can be a raw ingredient, end product, or any
level of
intermediate mix.

BOM is not overly easy to work with, but sometime it is the best
structure.

Okay, Graeme. You are much closer to the data than my very limited
understanding of your situation permits me to be, so you are probably on the
right track here.

A single memo field to spell out the mixing steps is fine. (A related table
with a record for each step is probably overkill.)

Regarding inspections, yes: I imagine you will have a table of inspections,
indicated who (foreign key to employee table) inspected what (foreign key to
product table) when (date/time.) It will just be one table if each
inspection is considered independent of the others.

Hopefully this structure will serve you well. It's certainly very flexible.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Graeme at Raptup" wrote in
message
...
Allen,
it's working like a charm.
THANK YOU.

FYI I have added two fields to the Product table (Yes/No) for Recipe and
Mix.
That way I have created a query to list only Recipes & Mixes for user
forms.
Make sense?

Also, I have created a form (Product query as above) with a subform
(ProductInProduct). I have inserted a lookup field for ProductID &
ProductName that inserts data into SourceProductID. It seems to work but
is
it correct?

One more question. The memo field spelling out the steps (mix method). Why
can't I just add a field to Product.ProductID? This field is only relevant
to
Mixes.

Finally, the Raw Material Inspection. As each ingredient (product) can be
inspected many times I assume that Product.ProductID is linked to a table
RawMaterialInspection (with ProductID as a foreign key)?
Thanks again,
Graeme

"Allen Browne" wrote:

Replies in-line

"Graeme at Raptup" wrote in
message
news
Hi Allen,
please bear with me - I'm sure this is frustrating for you.
I get the concept and can see how the ingredient can be a raw
ingredient
or
an intermediate product.

I am a bit thrown by the lack of relationship links between the tables,

You do create relationships between the tables I suggested.

Add a 2nd copy of the Product table to the Relationships window.
Access will alias it as Product_1.
You then create a relation from:
Product.ProductID = ProductInProduct.TargetProductID
and another relation from
Product_1.ProductID = ProductInProduct.SourceProductID

so I assume the (BOM) VB script handles this. I also don't know what
the 'Form1' function is, is it necessary?

Presumably this if from the example, which I haven't looked at for some
years.

Your tables look the same as those in BOM, Product (Component) and
ProductinProduct (Assembly). Fine. Is the UnitID a replacement of
'AssemblyBoolean'. Does it indicate a final product?

I'm guessing that in a recipe, some ingredients are measured in grams,
some
in mililitres, some in cups, etc. If so, you will have a
UnitOfMeasurement
table containing all the valid measurement types. The UnitID field is
meant
to be a foreign key to that table. The Quantity field is just a Number
(size
Double.) So the combination of Quantity + UnitID can say things like:
2 cups
500 grams


Your tables read;
Product (ProductID, ProductName)
ProductinProduct (TargetProductID, SourceProductID, Quantity, UnitID)

What happens to the Output table?
Does the recipe form a table at all? If so does it link with
ingredients?

Assuming your factory has only one recipe to make any product, the
ProductInProduct table gives you the ingredients for the recipe. You
might
want another related table that contains a memo field for spelling out
the
steps of the recipe, but you already have the ingredients listed.

Would the batch look something like this?
Batch (BatchID, RecipeID, CreateDate, UseByDate)

Yes, that's the idea. If there is only one recipe for any product you
make,
then you might be able to use ProductID instead of RecipeID. If a batch
might be double-quantity, you might want another Factor field (type
Number,
size Double.)

Sorry about my confusion, I'm just having difficulty finding how to
link
the
BOM to the rest of the database.

This structure is likely to be quite different from whatever you already
have in mind for the rest of the database.


Cheers,

Graeme



"Graeme at Raptup" wrote:


Allen,
Thanks a lot for your help.
I'm pretty sure I should be able to get around from here.
Cheers,
Graeme

"Allen Browne" wrote:


The basic idea is that a product is made from other products, so you
have
tables like this:

Product table:
ProductID primary key
ProductName Text

ProductInProduct table:
TargetProductID foreign key
SourceProductID foreign key
Quantity number
UnitID foreign key

So the records in the ProductInProduct table look like this:
TargetProduct SourceProduct
========== ============
1 (Toffee pudding) 2 (toffee pudding base)
1 (Toffee pudding) 3 (toffee pudding sauce)
1 (Toffee pudding) 4 (toffee pudding icing)
2 (Toffee pud. base) 5 brown sugar
2 (Toffee pud. base) 6 butter
3 (Toffee pud. sauce) 7 water
3 (Toffee pud. sauce) 8 thickening
4 ...

A raw ingredient has an entry in the Product table only (not in
ProductInProduct.)

An intermediate product (such as a sauce) has an entry in the
Product
table,
and multiple entries in the ProductInProduct table (one for each of
its
ingredients.)

An end product has an entry in the Product table, and multiple
entries
in
the ProductInProduct table (one for each ingredient, and these
ingredients
may themselves be interemediate products.)

The recipes are generic instructions of how to make each product.
The batches are specific instances of the recipe being used (with a
created-date and a use-by date.)

"Graeme at Raptup" wrote
in
message
...
Hi Allen,
I'm coming round to the BOM concept, but don't know how to link to
batches
or Raw Material Inspection.
I was going to have a table 'Recipe' that has many 'Mix'. The 'RM
Inspection' (many) relates to (one) 'Ingredient'. My thinking was
that the
'Ingredient' is linked to the 'Mix' rather than the 'Recipe'.
However
I
then
get stuck assigning quantities to ingredients. Which brings me
back
to the
BOM..!
Would it be ok to build my other tables around the BOM? I'm
thinking
of
taking the BOM and changing the names (e.g. Components to
Ingredients).
Clearly I'd have to go into the VB and change these.
Any suggestions on how to apply the BOM to my db?

"Allen Browne" wrote:

Ultimately, it's up to you as to whether the BOM approach suits
your
data
best, but the core idea is that everything you inspect is in the
one
table:
raw ingredients, mixes, mixes that use other mixes, and end
products.

The advantage of the BOM structure is the flexibility it
provides.
If you
don't do it this way, how will you model mixes that use other
mixes?
On
in
your Inspection table, how will you have a foreign key field that
can
relate
to end products, raw ingredients, and all levels of mixes in
between?
Even
in your Recipe table, you need a foreign key field that could be
a
raw
ingredient, or could be a mix; do you see that if you have
separate
tables
for Ingredient and Mix, your key field cannot connect to both?

Another core concept (separate from the BOM idea) is the
difference
between
a mix (a list of ingredients and quantities, like a sub-recipe)
and
a
batch
(a specific instance of a mix). A recipe must be able to refer to
a
mix.
A
batch is made from the list in the mix, on a particular date, and
goes
bad
if not used. The mix is just the concoction you use to make your
batches.

Hope that's helping you work through the way your data connects.

"Graeme at Raptup"
wrote
in
message
...
Ok Allen,
I downloaded and went through that BOM sample.
I recognise that it works on a Boolean loop but I am a bit
thrown
by
the
VB
detail.
I don't understand how the Mix (Assembly) becomes my batch. The
batch
needs
to link to various other tables.
Neither do I understand where the raw material inspection comes
in.
Note that the raw material inspection is a different process to
the
finished
product evaluation.
I can't help but think I have not got the normalisation right!

"Allen Browne" wrote:

Okay a key sticking point is the relation between an
inspection
and
the
other tables.

It seems you inspect at several points, so one of the things
you
might
want
to consider is a bill-of-materials type structu
http://www.mvps.org/access/modules/mdl0027.htm

With this structure, an ingredient is a "product".
The ingrediant goes into the mix, and the resultant batch is
also
a
"product."
The next mix is made from products: some raw ingredients, some
mixes,
but
since both are in the Product table, that's as very easy
relationship.

So, ultimately you inspect products. With the BOM structure,
the
product
being inspected can be a raw ingredient, end product, or any
level of
intermediate mix.

BOM is not overly easy to work with, but sometime it is the
best
structure.

Damn,
I can't make the Batch/RMInspection/Product link.
On our Batch Sheet (Lets say for Toffee Pudding Base) there are many
subsections (Loaded, Mixed, Deposited, etc).
For the Loaded section there is a LoadedBy field, and then all the
ingredients (Sugar, Egg, Jam) Each ingredient has a weight (as per
ProductInProduct table) but also has two more fields: RMCode and Checked (Y/N)

I had created two more tables: Batch and RMCode
Batch = BatchID, ProductID, CreateDate, UseByDate
RMCode = RMCodeID, ProductID, Supplier, Date

Just when you think it's sorted...

Hi Allen,
I thought I had this figured out, but.....

I'm having trouble either (a) with my relationships or (b) setting up forms.

I need to set up a form or capture data from a table that looks like this;

Batch# Mix (ProductName)
LoadedBy
Date

RM Code Ingredient (ProductName) Weight Checked (Y/N)
:::::::::::::: ::::::::::::::: ::::::::::: ::::::::::::::::::::::
a1 Sugar 5.23 Y
ve234 Egg 2.34 Y
Water 1 N

In the relationships window I have Batch.BatchID and ProductID as a foreign
key to the Product.ProductID table. I also have a table RMCode with productID
also as a foreign key to the Product table.

When it comes to designing the forms I just can't seem to create a form +
subform that works. (I've tried a number of permutations)

I have figured out how to create a recipe form and a form to create new
batches. I have tried different queries and then adding them in the form
wizard but with no joy.
My logic tells me that my tables are right, maybe I'm linking the wrong
product table?
Or is my form design poor?

Thanks again,
Graeme



"Allen Browne" wrote:

Okay, Graeme. You are much closer to the data than my very limited
understanding of your situation permits me to be, so you are probably on the
right track here.

A single memo field to spell out the mixing steps is fine. (A related table
with a record for each step is probably overkill.)

Regarding inspections, yes: I imagine you will have a table of inspections,
indicated who (foreign key to employee table) inspected what (foreign key to
product table) when (date/time.) It will just be one table if each
inspection is considered independent of the others.

Hopefully this structure will serve you well. It's certainly very flexible.

--

Access will probably get confused when you create the subform, because you
have 2 foreign keys on this table.

Create the subform and drag it onto your main form. Still in design view,
right-click the edge of the subform control, and choose Properties. On the
Data tab of the Properties box, set the LinkMasterFields to ProductID (the
name of the field in the main form), and LinkChildFields to SourceProductID
(the name of the matching field in the subform.)

The subform should then show the ingredients for the product in the main
form.

Hope I've understood: I'm not really retaining this whole thread in my
thinking at present.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Graeme at Raptup" wrote in
message
...
Hi Allen,
I thought I had this figured out, but.....

I'm having trouble either (a) with my relationships or (b) setting up
forms.

I need to set up a form or capture data from a table that looks like this;

Batch# Mix (ProductName)
LoadedBy
Date

RM Code Ingredient (ProductName) Weight Checked (Y/N)
:::::::::::::: ::::::::::::::: ::::::::::: ::::::::::::::::::::::
a1 Sugar 5.23 Y
ve234 Egg 2.34 Y
Water 1 N

In the relationships window I have Batch.BatchID and ProductID as a
foreign
key to the Product.ProductID table. I also have a table RMCode with
productID
also as a foreign key to the Product table.

When it comes to designing the forms I just can't seem to create a form +
subform that works. (I've tried a number of permutations)

I have figured out how to create a recipe form and a form to create new
batches. I have tried different queries and then adding them in the form
wizard but with no joy.
My logic tells me that my tables are right, maybe I'm linking the wrong
product table?
Or is my form design poor?

Thanks again,
Graeme



"Allen Browne" wrote:

Okay, Graeme. You are much closer to the data than my very limited
understanding of your situation permits me to be, so you are probably on
the
right track here.

A single memo field to spell out the mixing steps is fine. (A related
table
with a record for each step is probably overkill.)

Regarding inspections, yes: I imagine you will have a table of
inspections,
indicated who (foreign key to employee table) inspected what (foreign key
to
product table) when (date/time.) It will just be one table if each
inspection is considered independent of the others.

Hopefully this structure will serve you well. It's certainly very
flexible.

--

I'm afraid that did not work.
What you had suggested is similar to my recipe form, where Product creates
the main form and ProductInProduct creates the subform.

However the introduction of Batch as the main form does not seem to work.

I have created two new tables:
Batch = BatchID, ProductID, CreateDate, UseByDate
RMCode = RMCodeID, ProductID, Supplier, Date

And the other tables a
Product = ProductID, ProductName,Recipe (Y/N), Mix (Y/N)
ProductInProduct = TargetProductID, SourceProductID, Weight
and in the relationship window we have Product1 as the Product table copy.

I have linked the RM Code.ProductID (many) to Product.ProductID (one) and
the Batch.ProductID to Product.ProductID.

Am I linking the right tables?
Should I be creating some type of query before creating forms?

This is doing my nut in, I'm sure it's the same for you.

Cheers,

Graeme

"Allen Browne" wrote:

Access will probably get confused when you create the subform, because you
have 2 foreign keys on this table.

Create the subform and drag it onto your main form. Still in design view,
right-click the edge of the subform control, and choose Properties. On the
Data tab of the Properties box, set the LinkMasterFields to ProductID (the
name of the field in the main form), and LinkChildFields to SourceProductID
(the name of the matching field in the subform.)

The subform should then show the ingredients for the product in the main
form.

Hope I've understood: I'm not really retaining this whole thread in my
thinking at present.

Graeme, I may need to let this thread go.

The BOM structure is very handy where you have things that do into other
things that go into still other things. Its strength is its flexibility. Its
weakness is that it is less obvious to set up (especially the first time you
do one.) It seems ideal for what you are doing though.

All the best

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Graeme at Raptup" wrote in
message
...
I'm afraid that did not work.
What you had suggested is similar to my recipe form, where Product creates
the main form and ProductInProduct creates the subform.

However the introduction of Batch as the main form does not seem to work.

I have created two new tables:
Batch = BatchID, ProductID, CreateDate, UseByDate
RMCode = RMCodeID, ProductID, Supplier, Date

And the other tables a
Product = ProductID, ProductName,Recipe (Y/N), Mix (Y/N)
ProductInProduct = TargetProductID, SourceProductID, Weight
and in the relationship window we have Product1 as the Product table copy.

I have linked the RM Code.ProductID (many) to Product.ProductID (one) and
the Batch.ProductID to Product.ProductID.

Am I linking the right tables?
Should I be creating some type of query before creating forms?

This is doing my nut in, I'm sure it's the same for you.

Cheers,

Graeme

"Allen Browne" wrote:

Access will probably get confused when you create the subform, because
you
have 2 foreign keys on this table.

Create the subform and drag it onto your main form. Still in design view,
right-click the edge of the subform control, and choose Properties. On
the
Data tab of the Properties box, set the LinkMasterFields to ProductID
(the
name of the field in the main form), and LinkChildFields to
SourceProductID
(the name of the matching field in the subform.)

The subform should then show the ingredients for the product in the main
form.

Hope I've understood: I'm not really retaining this whole thread in my
thinking at present.