B
babs
See Below, I did all as FOLLOWS however get stuck on the advice AFter I
created the Batch and Batch Ingredients Table. Should the Quantity to be
produce be in the BAtch table. Not sure how to DO the REst that is after
that - how to execute an insert query to insert the records into
Batchingredients with the BatchID.......
Please help - Thanks so much,
Barb
Hi, Babs.
First, I am going to assume that this is an application you intend to do
completely in Access once you have your basic percentage formulas entered,
i.e., there will be no ongoing requirement to import data from Excel.
If this is the case, I think you will require several tables for this job.
All of them are simple in themselves; splitting them into multiple tables
will make using it so much easier than in Excel alone because you will be
harnessing the power of a relational database.
You didn’t mention it in this post, but I’m inferring from your initial post
that you intend also to keep a record of each batch that would include the
quantity of final product produced, the final product batch number, perhaps
the date and/or operator, and the batch number of each raw material used. Is
this correct?
If this is so, you have the following relationships to account for in your
application:
Batch (one) to Batch Ingredients (many)
Product (many) to Ingredients (many)
The latter means a product may have many ingredients, and an ingredient may
be used in many products. The best way to represent this relationship is
with two one-to-many relationships. Based on what you’ve told me, I suggest
the following tables to capture the basic formulas:
Products
----------
ProductID AutoNumber or Integer (Primary Key)
Product Text
Density Single or Double Floating Point
DefaultBatchSize Number
Unit Text or Numeric Foreign Key to a Units table
…any other product-specific information
Ingredients
-------------
IngredientID AutoNumber or Integer (PK)
Ingredient Text
Density Single or Double Floating Point
…any other raw material-specific information
ProductIngredients
---------------------
ProductIngredientID AutoNumber (PK)
ProductID Integer (Foreign Key to Products)
IngredientID Integer (Foreign Key to Ingredients)
Fraction Single or Double Floating Point
The first two tables store the basic information about all of your Products
and raw materials, respectively. The last stores your basic formula to
produce 1 unit of product, and represents the 1-to-many relationships to each
of them.
To enter the formulae, I would create a main form based on the Products
table, and a subform based on ProductIngredients, representing foreign keys
with combo boxes to facilitate the data entry. A control in the subform’s
footer would be handy to ensure that the total of the fraction field = 1.0
(=Sum([Fraction]).
Once the basic products, raw materials, and product ingredients have been
entered, it will be easy to calculate the required amounts for any size
batch: [Fraction]*[BatchSize]
To capture the information, I suggest two additional tables:
Batch
--------
BatchID AutoNumber or Integer or Text (PK)
ProductID Integer
BatchDate Date/Time
…any other Batch-specific information
BatchIngredients
-------------------
BatchIngID AutoNumber (PK)
BatchID Foreign Key to Batch (Match Type with BatchID)
RawMaterialID Integer (Foreign Key to RawMaterials)
BatchNumber Integer or Text
The way I see this working is you enter a new batch number, select the
Product ID from a combo box, and enter the Qty to be produced. At some
event, either by pressing a button or from the combo box’ AfterUpdate event,
you will need to execute an insert query to insert records into
BatchIngredients with the BatchID entered and the RawMaterialID from the
ProductIngredients table for each ingredient in this product. Probably the
easiest way to do this is to create the multi-table query in Design mode and
then call it using the OpenQuery method.
On a form based on Batch with a subform based on BatchIngredients, the
operator can enter the BatchNumber(s) for each ingredient.
A lot of work to be sure, to set it up. But simple when you’re done.
HTH
Sprinks
created the Batch and Batch Ingredients Table. Should the Quantity to be
produce be in the BAtch table. Not sure how to DO the REst that is after
that - how to execute an insert query to insert the records into
Batchingredients with the BatchID.......
Please help - Thanks so much,
Barb
Hi, Babs.
First, I am going to assume that this is an application you intend to do
completely in Access once you have your basic percentage formulas entered,
i.e., there will be no ongoing requirement to import data from Excel.
If this is the case, I think you will require several tables for this job.
All of them are simple in themselves; splitting them into multiple tables
will make using it so much easier than in Excel alone because you will be
harnessing the power of a relational database.
You didn’t mention it in this post, but I’m inferring from your initial post
that you intend also to keep a record of each batch that would include the
quantity of final product produced, the final product batch number, perhaps
the date and/or operator, and the batch number of each raw material used. Is
this correct?
If this is so, you have the following relationships to account for in your
application:
Batch (one) to Batch Ingredients (many)
Product (many) to Ingredients (many)
The latter means a product may have many ingredients, and an ingredient may
be used in many products. The best way to represent this relationship is
with two one-to-many relationships. Based on what you’ve told me, I suggest
the following tables to capture the basic formulas:
Products
----------
ProductID AutoNumber or Integer (Primary Key)
Product Text
Density Single or Double Floating Point
DefaultBatchSize Number
Unit Text or Numeric Foreign Key to a Units table
…any other product-specific information
Ingredients
-------------
IngredientID AutoNumber or Integer (PK)
Ingredient Text
Density Single or Double Floating Point
…any other raw material-specific information
ProductIngredients
---------------------
ProductIngredientID AutoNumber (PK)
ProductID Integer (Foreign Key to Products)
IngredientID Integer (Foreign Key to Ingredients)
Fraction Single or Double Floating Point
The first two tables store the basic information about all of your Products
and raw materials, respectively. The last stores your basic formula to
produce 1 unit of product, and represents the 1-to-many relationships to each
of them.
To enter the formulae, I would create a main form based on the Products
table, and a subform based on ProductIngredients, representing foreign keys
with combo boxes to facilitate the data entry. A control in the subform’s
footer would be handy to ensure that the total of the fraction field = 1.0
(=Sum([Fraction]).
Once the basic products, raw materials, and product ingredients have been
entered, it will be easy to calculate the required amounts for any size
batch: [Fraction]*[BatchSize]
To capture the information, I suggest two additional tables:
Batch
--------
BatchID AutoNumber or Integer or Text (PK)
ProductID Integer
BatchDate Date/Time
…any other Batch-specific information
BatchIngredients
-------------------
BatchIngID AutoNumber (PK)
BatchID Foreign Key to Batch (Match Type with BatchID)
RawMaterialID Integer (Foreign Key to RawMaterials)
BatchNumber Integer or Text
The way I see this working is you enter a new batch number, select the
Product ID from a combo box, and enter the Qty to be produced. At some
event, either by pressing a button or from the combo box’ AfterUpdate event,
you will need to execute an insert query to insert records into
BatchIngredients with the BatchID entered and the RawMaterialID from the
ProductIngredients table for each ingredient in this product. Probably the
easiest way to do this is to create the multi-table query in Design mode and
then call it using the OpenQuery method.
On a form based on Batch with a subform based on BatchIngredients, the
operator can enter the BatchNumber(s) for each ingredient.
A lot of work to be sure, to set it up. But simple when you’re done.
HTH
Sprinks