If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Making products - laying out forms/tables
I am putting together table/queries/forms to produce a product and need to
follow lot #s of raw materials put into them and back calcuate quantities of raw materials used based on the final quantity of product produced. I have started with one table of the list of raw material recieved. Example - raw materials #,name - 123(rubber),367(toluen),8989(mineral spirits) we'll say that these three products make tape ABC,1" masking tape which is the final product id and name. Every final product has a list of raw materials and percent of what goes in to make a given final quantity. I have an excel spreadsheet that lists each final product#, name and across as column headings is ALL the raw materials listed. example. final prod.name prod# rubber milk toluene mineral spts 1" masking tape abc .50 0 .34 .22 I feel like the raw material should be listed down instead of across. In the raw material table the items are listed down. I would like to generate a form for production when they are making the 1" tape(final prod) that would list the raw material that go into it and would give them a field to put in the lot # and the qty that they used next to each raw material. I can see grabbing this from a table created from the excel spreadsheet example but find it odd that the ex. rubber is now a field name in that table. Please help - on this table layout!!! Thanks,Barb |
#2
|
|||
|
|||
Babs,
Before importing the data into Access, do a Copy/Paste Special (Transpose) to rearrange the data so that the columns become rows (and vice versa). HTH Sprinks "babs" wrote: I am putting together table/queries/forms to produce a product and need to follow lot #s of raw materials put into them and back calcuate quantities of raw materials used based on the final quantity of product produced. I have started with one table of the list of raw material recieved. Example - raw materials #,name - 123(rubber),367(toluen),8989(mineral spirits) we'll say that these three products make tape ABC,1" masking tape which is the final product id and name. Every final product has a list of raw materials and percent of what goes in to make a given final quantity. I have an excel spreadsheet that lists each final product#, name and across as column headings is ALL the raw materials listed. example. final prod.name prod# rubber milk toluene mineral spts 1" masking tape abc .50 0 .34 .22 I feel like the raw material should be listed down instead of across. In the raw material table the items are listed down. I would like to generate a form for production when they are making the 1" tape(final prod) that would list the raw material that go into it and would give them a field to put in the lot # and the qty that they used next to each raw material. I can see grabbing this from a table created from the excel spreadsheet example but find it odd that the ex. rubber is now a field name in that table. Please help - on this table layout!!! Thanks,Barb |
#3
|
|||
|
|||
thanks for the advice on the transpose copy paste but still more questions.
Not quite sure how to set up the table. example of record?? finished prod. # finished prod name raw material code raw mat name lot # ABC 1" masking tape 232 rubber 45 2828 toluene 11 376 mineral spt. 49 As you see the finished prod # and name need to be listed once and then the ingredients that go into it will tak up numerous records if I list them down instead of across. I would like to generate a form automatically with the ingredients so production can enter the lot # they used without haveing to put the raw matl. code and name in. Is this a place where I need a sub form(subtable- is there such a thing?. Not sure how to set up the underlying table even though as in example above. Thanks, Barb "Sprinks" wrote: Babs, Before importing the data into Access, do a Copy/Paste Special (Transpose) to rearrange the data so that the columns become rows (and vice versa). HTH Sprinks "babs" wrote: I am putting together table/queries/forms to produce a product and need to follow lot #s of raw materials put into them and back calcuate quantities of raw materials used based on the final quantity of product produced. I have started with one table of the list of raw material recieved. Example - raw materials #,name - 123(rubber),367(toluen),8989(mineral spirits) we'll say that these three products make tape ABC,1" masking tape which is the final product id and name. Every final product has a list of raw materials and percent of what goes in to make a given final quantity. I have an excel spreadsheet that lists each final product#, name and across as column headings is ALL the raw materials listed. example. final prod.name prod# rubber milk toluene mineral spts 1" masking tape abc .50 0 .34 .22 I feel like the raw material should be listed down instead of across. In the raw material table the items are listed down. I would like to generate a form for production when they are making the 1" tape(final prod) that would list the raw material that go into it and would give them a field to put in the lot # and the qty that they used next to each raw material. I can see grabbing this from a table created from the excel spreadsheet example but find it odd that the ex. rubber is now a field name in that table. Please help - on this table layout!!! Thanks,Barb |
#4
|
|||
|
|||
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 "babs" wrote: thanks for the advice on the transpose copy paste but still more questions. Not quite sure how to set up the table. example of record?? finished prod. # finished prod name raw material code raw mat name lot # ABC 1" masking tape 232 rubber 45 2828 toluene 11 376 mineral spt. 49 As you see the finished prod # and name need to be listed once and then the ingredients that go into it will tak up numerous records if I list them down instead of across. I would like to generate a form automatically with the ingredients so production can enter the lot # they used without haveing to put the raw matl. code and name in. Is this a place where I need a sub form(subtable- is there such a thing?. Not sure how to set up the underlying table even though as in example above. Thanks, Barb "Sprinks" wrote: Babs, Before importing the data into Access, do a Copy/Paste Special (Transpose) to rearrange the data so that the columns become rows (and vice versa). HTH Sprinks "babs" wrote: I am putting together table/queries/forms to produce a product and need to follow lot #s of raw materials put into them and back calcuate quantities of raw materials used based on the final quantity of product produced. I have started with one table of the list of raw material recieved. Example - raw materials #,name - 123(rubber),367(toluen),8989(mineral spirits) we'll say that these three products make tape ABC,1" masking tape which is the final product id and name. Every final product has a list of raw materials and percent of what goes in to make a given final quantity. I have an excel spreadsheet that lists each final product#, name and across as column headings is ALL the raw materials listed. example. final prod.name prod# rubber milk toluene mineral spts 1" masking tape abc .50 0 .34 .22 I feel like the raw material should be listed down instead of across. In the raw material table the items are listed down. I would like to generate a form for production when they are making the 1" tape(final prod) that would list the raw material that go into it and would give them a field to put in the lot # and the qty that they used next to each raw material. I can see grabbing this from a table created from the excel spreadsheet example but find it odd that the ex. rubber is now a field name in that table. Please help - on this table layout!!! Thanks,Barb |
#5
|
|||
|
|||
thanks for the response. You are correct I have no intention in going back
to Excel. It is just right now that the products are listed across and each individual ingrediant is a COLUMN HEADING. I understand the 1st two table. One Raw Materials and its Vendor name, etc. The other the final product, Prod# and description. It is when I get the the table with the final product and what percent of what ingredient goes into it per 100# of item produced. I am not quite sure how to lay it out. Below is dummy data of copy/past of my "Formula" table. ProductIngredientID final prod.code Raw Matl.code quantityper100lb 1 12345 100020 0.25 2 12345 105406 0.27 3 12345 10029 0.21 4 12345 100093 0.22 Each final prod. has at least 15 raw matl that go into it. do I keep repeating the final prod. code. It's fine if that's what I have to do just not sure. The total percent doesn't always add up to 100 because we add water that dissipates off. Then would need an actual production table of # of lbs produced and back calc. (with no scrap) how much Raw mat. went into it.and generate a form from the raw material ingredient list for them to put in Lot# per Raw matl. Just need Clarity on this 3rd table mostly!!!!!! Thanks, Barb "Sprinks" wrote: 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 "babs" wrote: thanks for the advice on the transpose copy paste but still more questions. Not quite sure how to set up the table. example of record?? finished prod. # finished prod name raw material code raw mat name lot # ABC 1" masking tape 232 rubber 45 2828 toluene 11 376 mineral spt. 49 As you see the finished prod # and name need to be listed once and then the ingredients that go into it will tak up numerous records if I list them down instead of across. I would like to generate a form automatically with the ingredients so production can enter the lot # they used without haveing to put the raw matl. code and name in. Is this a place where I need a sub form(subtable- is there such a thing?. Not sure how to set up the underlying table even though as in example above. Thanks, Barb "Sprinks" wrote: Babs, Before importing the data into Access, do a Copy/Paste Special (Transpose) to rearrange the data so that the columns become rows (and vice versa). HTH Sprinks "babs" wrote: I am putting together table/queries/forms to produce a product and need to follow lot #s of raw materials put into them and back calcuate quantities of raw materials used based on the final quantity of product produced. I have started with one table of the list of raw material recieved. Example - raw materials #,name - 123(rubber),367(toluen),8989(mineral spirits) we'll say that these three products make tape ABC,1" masking tape which is the final product id and name. Every final product has a list of raw materials and percent of what goes in to make a given final quantity. I have an excel spreadsheet that lists each final product#, name and across as column headings is ALL the raw materials listed. example. final prod.name prod# rubber milk toluene mineral spts 1" masking tape abc .50 0 .34 .22 I feel like the raw material should be listed down instead of across. In the raw material table the items are listed down. I would like to generate a form for production when they are making the 1" tape(final prod) that would list the raw material that go into it and would give them a field to put in the lot # and the qty that they used next to each raw material. I can see grabbing this from a table created from the excel spreadsheet example but find it odd that the ex. rubber is now a field name in that table. Please help - on this table layout!!! Thanks,Barb |
#6
|
|||
|
|||
Yes, you need the ProductID for each record in order to tie the ingredient to
the product. The easiest way to do this, though is a continuous subform based on ProductIngredients placed on a main form based on Product. Include the ProductID on the main form but not on the subform. When you insert the subform, set its LinkMasterField and LinkChildField properties to ProductID to link them. Then you don't have to enter the ProductID on the detail form, Access does it for you. It also looks to me as if your Fraction field is "QuantityPerLB" rather than per 100 lbs., since it seems the total adds up to about 1, not 100. Also, if you're storing a vendor with each raw material, create a Vendor table, and store ONLY a numeric VendorID in your Ingredient table (a foreign key). BTW, Babs, you're making me nostalgic. I started my career as a formulation and QC chemist. One of my first tasks was entering our Bills of Material into our system! HTH Sprinks "babs" wrote: thanks for the response. You are correct I have no intention in going back to Excel. It is just right now that the products are listed across and each individual ingrediant is a COLUMN HEADING. I understand the 1st two table. One Raw Materials and its Vendor name, etc. The other the final product, Prod# and description. It is when I get the the table with the final product and what percent of what ingredient goes into it per 100# of item produced. I am not quite sure how to lay it out. Below is dummy data of copy/past of my "Formula" table. ProductIngredientID final prod.code Raw Matl.code quantityper100lb 1 12345 100020 0.25 2 12345 105406 0.27 3 12345 10029 0.21 4 12345 100093 0.22 Each final prod. has at least 15 raw matl that go into it. do I keep repeating the final prod. code. It's fine if that's what I have to do just not sure. The total percent doesn't always add up to 100 because we add water that dissipates off. Then would need an actual production table of # of lbs produced and back calc. (with no scrap) how much Raw mat. went into it.and generate a form from the raw material ingredient list for them to put in Lot# per Raw matl. Just need Clarity on this 3rd table mostly!!!!!! Thanks, Barb "Sprinks" wrote: 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 "babs" wrote: thanks for the advice on the transpose copy paste but still more questions. Not quite sure how to set up the table. example of record?? finished prod. # finished prod name raw material code raw mat name lot # ABC 1" masking tape 232 rubber 45 2828 toluene 11 376 mineral spt. 49 As you see the finished prod # and name need to be listed once and then the ingredients that go into it will tak up numerous records if I list them down instead of across. I would like to generate a form automatically with the ingredients so production can enter the lot # they used without haveing to put the raw matl. code and name in. Is this a place where I need a sub form(subtable- is there such a thing?. Not sure how to set up the underlying table even though as in example above. Thanks, Barb "Sprinks" wrote: Babs, Before importing the data into Access, do a Copy/Paste Special (Transpose) to rearrange the data so that the columns become rows (and vice versa). HTH Sprinks "babs" wrote: I am putting together table/queries/forms to produce a product and need to follow lot #s of raw materials put into them and back calcuate quantities of raw materials used based on the final quantity of product produced. I have started with one table of the list of raw material recieved. Example - raw materials #,name - 123(rubber),367(toluen),8989(mineral spirits) we'll say that these three products make tape ABC,1" masking tape which is the final product id and name. Every final product has a list of raw materials and percent of what goes in to make a given final quantity. I have an excel spreadsheet that lists each final product#, name and across as column headings is ALL the raw materials listed. example. final prod.name prod# rubber milk toluene mineral spts 1" masking tape abc .50 0 .34 .22 I feel like the raw material should be listed down instead of across. In the raw material table the items are listed down. I would like to generate a form for production when they are making the 1" tape(final prod) that would list the raw material that go into it and would give them a field to put in the lot # and the qty that they used next to each raw material. I can see grabbing this from a table created from the excel spreadsheet example but find it odd that the ex. rubber is now a field name in that table. Please help - on this table layout!!! Thanks,Barb |
#7
|
|||
|
|||
Thanks for the input on the Main form subform. That seems to be the idea
that I want. I have worked with Access and VB alot but somehow have not worked with subforms much at all. I need the production floor to input total #s of product produced and then do the back calculation on it for example (1bs produced * .327) /100 to get the quantity of one of the raw materials ideally consumed. I usually like to do the formulas on a query so I can pull it onto any form or report later - not sure where to put this input 1bls field and where to do the calc. to get a result for each Raw matl of "Actual amount consumed" Glad that you are familiar with this stuff - THANKS SOOO MUCH!! Barb "Sprinks" wrote: Yes, you need the ProductID for each record in order to tie the ingredient to the product. The easiest way to do this, though is a continuous subform based on ProductIngredients placed on a main form based on Product. Include the ProductID on the main form but not on the subform. When you insert the subform, set its LinkMasterField and LinkChildField properties to ProductID to link them. Then you don't have to enter the ProductID on the detail form, Access does it for you. It also looks to me as if your Fraction field is "QuantityPerLB" rather than per 100 lbs., since it seems the total adds up to about 1, not 100. Also, if you're storing a vendor with each raw material, create a Vendor table, and store ONLY a numeric VendorID in your Ingredient table (a foreign key). BTW, Babs, you're making me nostalgic. I started my career as a formulation and QC chemist. One of my first tasks was entering our Bills of Material into our system! HTH Sprinks "babs" wrote: thanks for the response. You are correct I have no intention in going back to Excel. It is just right now that the products are listed across and each individual ingrediant is a COLUMN HEADING. I understand the 1st two table. One Raw Materials and its Vendor name, etc. The other the final product, Prod# and description. It is when I get the the table with the final product and what percent of what ingredient goes into it per 100# of item produced. I am not quite sure how to lay it out. Below is dummy data of copy/past of my "Formula" table. ProductIngredientID final prod.code Raw Matl.code quantityper100lb 1 12345 100020 0.25 2 12345 105406 0.27 3 12345 10029 0.21 4 12345 100093 0.22 Each final prod. has at least 15 raw matl that go into it. do I keep repeating the final prod. code. It's fine if that's what I have to do just not sure. The total percent doesn't always add up to 100 because we add water that dissipates off. Then would need an actual production table of # of lbs produced and back calc. (with no scrap) how much Raw mat. went into it.and generate a form from the raw material ingredient list for them to put in Lot# per Raw matl. Just need Clarity on this 3rd table mostly!!!!!! Thanks, Barb "Sprinks" wrote: 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 "babs" wrote: thanks for the advice on the transpose copy paste but still more questions. Not quite sure how to set up the table. example of record?? finished prod. # finished prod name raw material code raw mat name lot # ABC 1" masking tape 232 rubber 45 2828 toluene 11 376 mineral spt. 49 As you see the finished prod # and name need to be listed once and then the ingredients that go into it will tak up numerous records if I list them down instead of across. I would like to generate a form automatically with the ingredients so production can enter the lot # they used without haveing to put the raw matl. code and name in. Is this a place where I need a sub form(subtable- is there such a thing?. Not sure how to set up the underlying table even though as in example above. Thanks, Barb "Sprinks" wrote: Babs, Before importing the data into Access, do a Copy/Paste Special (Transpose) to rearrange the data so that the columns become rows (and vice versa). HTH Sprinks "babs" wrote: I am putting together table/queries/forms to produce a product and need to follow lot #s of raw materials put into them and back calcuate quantities of raw materials used based on the final quantity of product produced. I have started with one table of the list of raw material recieved. Example - raw materials #,name - 123(rubber),367(toluen),8989(mineral spirits) we'll say that these three products make tape ABC,1" masking tape which is the final product id and name. Every final product has a list of raw materials and percent of what goes in to make a given final quantity. I have an excel spreadsheet that lists each final product#, name and across as column headings is ALL the raw materials listed. example. final prod.name prod# rubber milk toluene mineral spts 1" masking tape abc .50 0 .34 .22 I feel like the raw material should be listed down instead of across. In the raw material table the items are listed down. I would like to generate a form for production when they are making the 1" tape(final prod) that would list the raw material that go into it and would give them a field to put in the lot # and the qty that they used next to each raw material. I can see grabbing this from a table created from the excel spreadsheet example but find it odd that the ex. rubber is now a field name in that table. Please help - on this table layout!!! Thanks,Barb |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problems with Microsoft products | David H. Lipman | Setup, Installing & Configuration | 0 | September 21st, 2004 11:59 PM |
making an mde file | Alice Dawson | New Users | 5 | August 11th, 2004 05:47 PM |
Making comparisons with queries | Ellen | Running & Setting Up Queries | 0 | June 14th, 2004 02:48 PM |
adding up sum of several products is off | Kendra | Worksheet Functions | 2 | October 2nd, 2003 03:24 PM |