G
Graeme at Raptup
Hi,
I have managed to set up a BOM type relationship for a bakery recipe/batch
process (thanks to help from Allen Browne).
I am now able to create forms with subforms to build recipes, create new
batches and enter new ingredients.
However I have a problem setting up relationships and forms that extend
outside of this relationship.
I have the following tables in the relationship window:
Product ( ProductID, ProductName, Recipe(Y/N), Mix (Y/N))
ProductInProduct (TargetProductID, SourceProductID , Weight)
Product1 ( ProductID, ProductName, Recipe(Y/N), Mix (Y/N))
Batch (BatchID, ProductID, CreateDate, UseByDate, LoadedBy)
Product1.ProductID => ProductInProduct.SourceProductID
A recipe (e.g. Toffee Pudding) consists of a Mix or ‘sub recipes’ (e.g.
Pudding Base, Sauce) and each in turn is comprised of ingredients.
Most (Raw) ingredients are inspected. So for example, Flour may be inspected
on a given date and allocated a RM Code (Raw Material).
Each Mix made represents a batch and is Loaded by a person on a date. A
Batch sheet comprises of 9 steps and it is the first that is my problem. I
need to replicate something like the detail below:
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
Etc.
I managed to create a form that gets me half way there by creating two
separate queries and combining them to build the form. I also guess there
should be a table for the RMCode which would be one ProductID to many RMCode.
But I don’t know to which table that relationship should be created.
My two queries to create this form are:
SELECT Batch.BatchID, Batch.LoadedBy, Batch.CreateDate, Batch.UseByDate,
Product.ProductName, Product.Mix
FROM Product INNER JOIN Batch ON Product.ProductID = Batch.ProductID
WHERE (((Product.Mix)=Yes));
And the other query is;
SELECT ProductInProduct.SourceProductID, ProductInProduct.[Weight kgs],
Product_1.ProductName
FROM Product INNER JOIN (Product AS Product_1 INNER JOIN ProductInProduct ON
Product_1.ProductID = ProductInProduct.SourceProductID) ON Product.ProductID
= ProductInProduct.TargetProductID;
How do I get to create the form as per the 'form' above (or forms if need
be)? Any suggestions appreciated!
I have managed to set up a BOM type relationship for a bakery recipe/batch
process (thanks to help from Allen Browne).
I am now able to create forms with subforms to build recipes, create new
batches and enter new ingredients.
However I have a problem setting up relationships and forms that extend
outside of this relationship.
I have the following tables in the relationship window:
Product ( ProductID, ProductName, Recipe(Y/N), Mix (Y/N))
ProductInProduct (TargetProductID, SourceProductID , Weight)
Product1 ( ProductID, ProductName, Recipe(Y/N), Mix (Y/N))
Batch (BatchID, ProductID, CreateDate, UseByDate, LoadedBy)
Product1.ProductID => ProductInProduct.SourceProductID
A recipe (e.g. Toffee Pudding) consists of a Mix or ‘sub recipes’ (e.g.
Pudding Base, Sauce) and each in turn is comprised of ingredients.
Most (Raw) ingredients are inspected. So for example, Flour may be inspected
on a given date and allocated a RM Code (Raw Material).
Each Mix made represents a batch and is Loaded by a person on a date. A
Batch sheet comprises of 9 steps and it is the first that is my problem. I
need to replicate something like the detail below:
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
Etc.
I managed to create a form that gets me half way there by creating two
separate queries and combining them to build the form. I also guess there
should be a table for the RMCode which would be one ProductID to many RMCode.
But I don’t know to which table that relationship should be created.
My two queries to create this form are:
SELECT Batch.BatchID, Batch.LoadedBy, Batch.CreateDate, Batch.UseByDate,
Product.ProductName, Product.Mix
FROM Product INNER JOIN Batch ON Product.ProductID = Batch.ProductID
WHERE (((Product.Mix)=Yes));
And the other query is;
SELECT ProductInProduct.SourceProductID, ProductInProduct.[Weight kgs],
Product_1.ProductName
FROM Product INNER JOIN (Product AS Product_1 INNER JOIN ProductInProduct ON
Product_1.ProductID = ProductInProduct.SourceProductID) ON Product.ProductID
= ProductInProduct.TargetProductID;
How do I get to create the form as per the 'form' above (or forms if need
be)? Any suggestions appreciated!