Relationship problem

Hi,
I am trying to set up a db (Access 2002) for a food production environment
(bakery).
The relationships I have identified a
1 Recipe (e.g. Toffee Pudding) made of many Mixes (e.g. Base, Sauce1, Sauce2)
Each Mix has many ingredients (each ingredient has weights)
Each Mix has a mixing method (Guess therefore it can stay in the mix table)
Each Mix is assigned a Batch number
Each Batch sheet has (1) Ingredients loaded by one person, date. Each
ingredient has weight (see Mix) is checked and has a Raw Material Inspection
code. (2) is Mixed By (Date, machine, times), (3)Deposited By (Date, machine,
etc), (4) and a number of similar processes.
Each Product is Evaluated when it is produced (with a reference to the Batch
#)
There is a Raw Material Inspection sheet that is tested against ingredients
(Note that not all ingredients are inspected)

The Raw Material Inspection (RMI)code is found on the Batch sheet against
the ingredients.
My main problem is creating a form for the batch sheet. It is (1) that is
causing my frustration.
At the moment I have 1 Ingredients to Many RMI. I also have 1 Ingredient in
many MixMethod, 1 MixMethod to Many Loaded and 1 Batch to Many Loaded.

Messy, I know but can't seem to streamline. My problem is also getting the
RMI code against each ingredient.

Any help appreciated! I've been going through permutations all day!

If you have only spent one day on it, it's early days. I've spent weeks on
some database designs. It might help to model it visually with a tool like
Visio.
I dont have any specific recommendations because I don't know the bakery
business. Just make sure your tables are normalized. I would have thought you
would need to cater for different sizes in your recipes i.e. everything else
stays the same except you are baking 2000 loaves instead of 1000.

-Dorian

"Graeme at Raptup" wrote:

Hi,
I am trying to set up a db (Access 2002) for a food production environment
(bakery).
The relationships I have identified a
1 Recipe (e.g. Toffee Pudding) made of many Mixes (e.g. Base, Sauce1, Sauce2)
Each Mix has many ingredients (each ingredient has weights)
Each Mix has a mixing method (Guess therefore it can stay in the mix table)
Each Mix is assigned a Batch number
Each Batch sheet has (1) Ingredients loaded by one person, date. Each
ingredient has weight (see Mix) is checked and has a Raw Material Inspection
code. (2) is Mixed By (Date, machine, times), (3)Deposited By (Date, machine,
etc), (4) and a number of similar processes.
Each Product is Evaluated when it is produced (with a reference to the Batch
#)
There is a Raw Material Inspection sheet that is tested against ingredients
(Note that not all ingredients are inspected)

The Raw Material Inspection (RMI)code is found on the Batch sheet against
the ingredients.
My main problem is creating a form for the batch sheet. It is (1) that is
causing my frustration.
At the moment I have 1 Ingredients to Many RMI. I also have 1 Ingredient in
many MixMethod, 1 MixMethod to Many Loaded and 1 Batch to Many Loaded.

Messy, I know but can't seem to streamline. My problem is also getting the
RMI code against each ingredient.

Any help appreciated! I've been going through permutations all day!

Thanks,
I don't have Visio but will give it a look. I have drawn up some entity
relationship diagrams but seem to get stuck on the Inspection part.
Yes you are right about the quantity but in their scenario they work with
weight.


"mscertified" wrote:

If you have only spent one day on it, it's early days. I've spent weeks on
some database designs. It might help to model it visually with a tool like
Visio.
I dont have any specific recommendations because I don't know the bakery
business. Just make sure your tables are normalized. I would have thought you
would need to cater for different sizes in your recipes i.e. everything else
stays the same except you are baking 2000 loaves instead of 1000.

-Dorian

"Graeme at Raptup" wrote:

Hi,
I am trying to set up a db (Access 2002) for a food production environment
(bakery).
The relationships I have identified a
1 Recipe (e.g. Toffee Pudding) made of many Mixes (e.g. Base, Sauce1, Sauce2)
Each Mix has many ingredients (each ingredient has weights)
Each Mix has a mixing method (Guess therefore it can stay in the mix table)
Each Mix is assigned a Batch number
Each Batch sheet has (1) Ingredients loaded by one person, date. Each
ingredient has weight (see Mix) is checked and has a Raw Material Inspection
code. (2) is Mixed By (Date, machine, times), (3)Deposited By (Date, machine,
etc), (4) and a number of similar processes.
Each Product is Evaluated when it is produced (with a reference to the Batch
#)
There is a Raw Material Inspection sheet that is tested against ingredients
(Note that not all ingredients are inspected)

The Raw Material Inspection (RMI)code is found on the Batch sheet against
the ingredients.
My main problem is creating a form for the batch sheet. It is (1) that is
causing my frustration.
At the moment I have 1 Ingredients to Many RMI. I also have 1 Ingredient in
many MixMethod, 1 MixMethod to Many Loaded and 1 Batch to Many Loaded.

Messy, I know but can't seem to streamline. My problem is also getting the
RMI code against each ingredient.

Any help appreciated! I've been going through permutations all day!

Graeme, I don't understand the baking process, so I may be way off here.
Part of the issue is identifying all the one-to-many relations correctly,
and determining if any of them are actually many-to-many.

Is it ever the case that you mix up a batch of something, and then use part
of the batch to make different products? Like the base for a scone mix, that
is also used to make tea cakes? Or a bread mix, that is also used to make
pizza bases?

Are there multiple levels of mixing here? Say you mix up a basic ingredient
(a sauce), and then the next mix contains not only raw ingredients, but the
sauce from the previous mix as an ingredient?

It seems that a product is made in stages, where a stage consists of several
components/operations, such as load, mix, deposit. This particular area
needs quite a bit more thought, I suspect, to break it down into a series of
one-to-many relations.

Finally, the inspection process: *what* precisely is inspected?
Do you inspect ingredients periodically?
Are batches inspected?
Are the product stages inspected?
Is the final product inspected?

Does one inspection consist of many inspection aspects (steps, or batches)?
With multiple times? dates? locations? Are there different kinds of
inspections applicable to different batches, or different stages?

Sorry that I am not able to contribute much to actual data structure because
of my ignorance of the process. But hopefully some of those question will
help you to identify how the pieces should fit together, i.e. where all the
one-to-manys lie in modelling your production process.

--
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,
I am trying to set up a db (Access 2002) for a food production environment
(bakery).
The relationships I have identified a
1 Recipe (e.g. Toffee Pudding) made of many Mixes (e.g. Base, Sauce1,
Sauce2)
Each Mix has many ingredients (each ingredient has weights)
Each Mix has a mixing method (Guess therefore it can stay in the mix
table)
Each Mix is assigned a Batch number
Each Batch sheet has (1) Ingredients loaded by one person, date. Each
ingredient has weight (see Mix) is checked and has a Raw Material
Inspection
code. (2) is Mixed By (Date, machine, times), (3)Deposited By (Date,
machine,
etc), (4) and a number of similar processes.
Each Product is Evaluated when it is produced (with a reference to the
Batch
#)
There is a Raw Material Inspection sheet that is tested against
ingredients
(Note that not all ingredients are inspected)

The Raw Material Inspection (RMI)code is found on the Batch sheet against
the ingredients.
My main problem is creating a form for the batch sheet. It is (1) that is
causing my frustration.
At the moment I have 1 Ingredients to Many RMI. I also have 1 Ingredient
in
many MixMethod, 1 MixMethod to Many Loaded and 1 Batch to Many Loaded.

Messy, I know but can't seem to streamline. My problem is also getting the
RMI code against each ingredient.

Any help appreciated! I've been going through permutations all day!

Hi Allen,
you have helped me before, so thanks again.
If you don't mind I"ll add a process flow here and I'll reply to your
questions below;

1. Recipe is developed for a product (e.g. Toffee Pudding)
2. Recipe includes sub-products (e.g. Base, sauce, icing)
3. Each sub-product has ingredients with corresponding weights and brand
(Supplier)
4. Each recipe has a yield (e.g. 1 trolley), bake temp, bake time and scaling
5. Sub products each have a mixing method
6. When a recipe is prepared batches are opened for each sub-product
7. Batches are loaded and ingredients are checked against weight and R/M
batch code (Raw Material Inspection)
8. Each batch follows a process from: Loading – Mixed By – Deposited By –
Ovenor Name – Released to Depanning – Released to Cake Finish – Sauce/Cake
Finish – Released to Wrapping – Cake Wrapping – QC approved (Not all
processes are compulsory)
9. The product is then evaluated. There are two forms for this (Sample and
Finished Product)
10. Some ingredients are inspected as per the RMI (Raw Material Inspection)
sheet. An inspection will include a Lot Number that is to be linked to the
Loading (7 above) process (If that ingredient has been checked). (If
necessary we can set up our own RMI code)


"Allen Browne" wrote:

Graeme, I don't understand the baking process, so I may be way off here.
Part of the issue is identifying all the one-to-many relations correctly,
and determining if any of them are actually many-to-many.

Is it ever the case that you mix up a batch of something, and then use part
of the batch to make different products? Like the base for a scone mix, that
is also used to make tea cakes? Or a bread mix, that is also used to make
pizza bases?

Perhaps the terminology is a bit misleading. But in this case a batch is
specific to a mix (or what I have also called a sub product). So no, one
batch only makes one mix.

Are there multiple levels of mixing here? Say you mix up a basic ingredient
(a sauce), and then the next mix contains not only raw ingredients, but the
sauce from the previous mix as an ingredient?
Yes, the way it works is that we have say a Toffee pudding (Recipe). The
recipe is made up of 3 components (Base, thick sauce and a thin sauce). This
could be 1 or it could be as many as 4 (e.g. icing).


It seems that a product is made in stages, where a stage consists of several
components/operations, such as load, mix, deposit. This particular area
needs quite a bit more thought, I suspect, to break it down into a series of
one-to-many relations.
Yes, I had in fact set up 1 to-M relationships for each of those (as in '8'
above). That part seemed to work, but based on the assumption that they all
linked to an entity called Batch. (I used BatchID as my key which is an
autonumber)


Finally, the inspection process: *what* precisely is inspected?
Do you inspect ingredients periodically?
Are batches inspected?
Are the product stages inspected?
Is the final product inspected?

Yes, this is the sticky part (Poor pun, I know).
Certain products such as liquified egg are tested upon receipt from the
supplier (each and every delivery). Water, for example is not tested in this
manner.
The supplier provides a Lot Number which is their unique code for each
delivery. That Lot Number (or I suppose we can assign our own code if need
be) must be assigned to each ingredient (where there is an inspection code)
in the Loading stage.
Each Loading is linked to a specific batch.
As per (7) above a Batch starts by being Loaded. Each Load is loaded by a
person, date, and all the ingredients of the sub-product/mix, each with an
inspection code, weight (pulled in from the mix table I presume) and a
Checked field.

Final product is Evaluated, but that is a different form/process to this
inspection.


Does one inspection consist of many inspection aspects (steps, or batches)?
With multiple times? dates? locations? Are there different kinds of
inspections applicable to different batches, or different stages?

Each (Raw Material) Inspection includes Date, Lot Number and various testing
fields (e.g. temperature). One Inspection is likely to be applicable to many
batches

Sorry that I am not able to contribute much to actual data structure because
of my ignorance of the process. But hopefully some of those question will
help you to identify how the pieces should fit together, i.e. where all the
one-to-manys lie in modelling your production process.

Thanks, I can't expect you to do the work for me! Hopefully you can see that
I have the basic relationships in place. My issue seems to be between the
mix/sub-product and the ingredients and the batch and the RM code.

By the way, big cricket game Friday?


--
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,
I am trying to set up a db (Access 2002) for a food production environment
(bakery).
The relationships I have identified a
1 Recipe (e.g. Toffee Pudding) made of many Mixes (e.g. Base, Sauce1,
Sauce2)
Each Mix has many ingredients (each ingredient has weights)
Each Mix has a mixing method (Guess therefore it can stay in the mix
table)
Each Mix is assigned a Batch number
Each Batch sheet has (1) Ingredients loaded by one person, date. Each
ingredient has weight (see Mix) is checked and has a Raw Material
Inspection
code. (2) is Mixed By (Date, machine, times), (3)Deposited By (Date,
machine,
etc), (4) and a number of similar processes.
Each Product is Evaluated when it is produced (with a reference to the
Batch
#)
There is a Raw Material Inspection sheet that is tested against
ingredients
(Note that not all ingredients are inspected)

The Raw Material Inspection (RMI)code is found on the Batch sheet against
the ingredients.
My main problem is creating a form for the batch sheet. It is (1) that is
causing my frustration.
At the moment I have 1 Ingredients to Many RMI. I also have 1 Ingredient
in
many MixMethod, 1 MixMethod to Many Loaded and 1 Batch to Many Loaded.

Messy, I know but can't seem to streamline. My problem is also getting the
RMI code against each ingredient.

Any help appreciated! I've been going through permutations all day!

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 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,
you have helped me before, so thanks again.
If you don't mind I"ll add a process flow here and I'll reply to your
questions below;

1. Recipe is developed for a product (e.g. Toffee Pudding)
2. Recipe includes sub-products (e.g. Base, sauce, icing)
3. Each sub-product has ingredients with corresponding weights and brand
(Supplier)
4. Each recipe has a yield (e.g. 1 trolley), bake temp, bake time and
scaling
5. Sub products each have a mixing method
6. When a recipe is prepared batches are opened for each sub-product
7. Batches are loaded and ingredients are checked against weight and R/M
batch code (Raw Material Inspection)
8. Each batch follows a process from: Loading – Mixed By – Deposited By –
Ovenor Name – Released to Depanning – Released to Cake Finish – Sauce/Cake
Finish – Released to Wrapping – Cake Wrapping – QC approved (Not all
processes are compulsory)
9. The product is then evaluated. There are two forms for this (Sample and
Finished Product)
10. Some ingredients are inspected as per the RMI (Raw Material
Inspection)
sheet. An inspection will include a Lot Number that is to be linked to the
Loading (7 above) process (If that ingredient has been checked). (If
necessary we can set up our own RMI code)


"Allen Browne" wrote:

Graeme, I don't understand the baking process, so I may be way off here.
Part of the issue is identifying all the one-to-many relations correctly,
and determining if any of them are actually many-to-many.

Is it ever the case that you mix up a batch of something, and then use
part
of the batch to make different products? Like the base for a scone mix,
that
is also used to make tea cakes? Or a bread mix, that is also used to make
pizza bases?

Perhaps the terminology is a bit misleading. But in this case a batch is
specific to a mix (or what I have also called a sub product). So no, one
batch only makes one mix.

Are there multiple levels of mixing here? Say you mix up a basic
ingredient
(a sauce), and then the next mix contains not only raw ingredients, but
the
sauce from the previous mix as an ingredient?
Yes, the way it works is that we have say a Toffee pudding (Recipe). The
recipe is made up of 3 components (Base, thick sauce and a thin sauce).
This
could be 1 or it could be as many as 4 (e.g. icing).


It seems that a product is made in stages, where a stage consists of
several
components/operations, such as load, mix, deposit. This particular area
needs quite a bit more thought, I suspect, to break it down into a series
of
one-to-many relations.
Yes, I had in fact set up 1 to-M relationships for each of those (as in
'8'
above). That part seemed to work, but based on the assumption that they
all
linked to an entity called Batch. (I used BatchID as my key which is an
autonumber)


Finally, the inspection process: *what* precisely is inspected?
Do you inspect ingredients periodically?
Are batches inspected?
Are the product stages inspected?
Is the final product inspected?

Yes, this is the sticky part (Poor pun, I know).
Certain products such as liquified egg are tested upon receipt from the
supplier (each and every delivery). Water, for example is not tested in
this
manner.
The supplier provides a Lot Number which is their unique code for each
delivery. That Lot Number (or I suppose we can assign our own code if need
be) must be assigned to each ingredient (where there is an inspection
code)
in the Loading stage.
Each Loading is linked to a specific batch.
As per (7) above a Batch starts by being Loaded. Each Load is loaded by a
person, date, and all the ingredients of the sub-product/mix, each with an
inspection code, weight (pulled in from the mix table I presume) and a
Checked field.

Final product is Evaluated, but that is a different form/process to this
inspection.


Does one inspection consist of many inspection aspects (steps, or
batches)?
With multiple times? dates? locations? Are there different kinds of
inspections applicable to different batches, or different stages?

Each (Raw Material) Inspection includes Date, Lot Number and various
testing
fields (e.g. temperature). One Inspection is likely to be applicable to
many
batches

Sorry that I am not able to contribute much to actual data structure
because
of my ignorance of the process. But hopefully some of those question will
help you to identify how the pieces should fit together, i.e. where all
the
one-to-manys lie in modelling your production process.

Thanks, I can't expect you to do the work for me! Hopefully you can see
that
I have the basic relationships in place. My issue seems to be between the
mix/sub-product and the ingredients and the batch and the RM code.

By the way, big cricket game Friday?


"Graeme at Raptup" wrote in
message
...
Hi,
I am trying to set up a db (Access 2002) for a food production
environment
(bakery).
The relationships I have identified a
1 Recipe (e.g. Toffee Pudding) made of many Mixes (e.g. Base, Sauce1,
Sauce2)
Each Mix has many ingredients (each ingredient has weights)
Each Mix has a mixing method (Guess therefore it can stay in the mix
table)
Each Mix is assigned a Batch number
Each Batch sheet has (1) Ingredients loaded by one person, date. Each
ingredient has weight (see Mix) is checked and has a Raw Material
Inspection
code. (2) is Mixed By (Date, machine, times), (3)Deposited By (Date,
machine,
etc), (4) and a number of similar processes.
Each Product is Evaluated when it is produced (with a reference to the
Batch
#)
There is a Raw Material Inspection sheet that is tested against
ingredients
(Note that not all ingredients are inspected)

The Raw Material Inspection (RMI)code is found on the Batch sheet
against
the ingredients.
My main problem is creating a form for the batch sheet. It is (1) that
is
causing my frustration.
At the moment I have 1 Ingredients to Many RMI. I also have 1
Ingredient
in
many MixMethod, 1 MixMethod to Many Loaded and 1 Batch to Many Loaded.

Messy, I know but can't seem to streamline. My problem is also getting
the
RMI code against each ingredient.

Any help appreciated! I've been going through permutations all day!

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 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.

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,
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.

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.