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
|
|||
|
|||
Attn Sprinks- Not duplicate insert records
Here is a little more info. just messed with the command button that inserts the records and was on a record on the main form batch where I already populated or inserted the ingredients that go into the product on the subform. when i pressed the command button again it inserted a whole additional 20 records basically duplicates without the lots# put in. How could I safe guard this so the operators can make this happen. thanks so much, barb Below was what was posted before. ITS WORKING SO FAR SO GOOD. AS I DEVELOP MORE I AM SURE I WILL HAVE MORE QUESTIONS. CAN I POST THEM ATTN. SPRINKS!!!! THANKS A TON - NOW I CAN GET SOME OTHER STUFF DONE!!! "babs" wrote: The SQL worked however it is inserting the records into the BatchIngredients table which is what we want to do. The semicode is not inserted - don't we want that in there. Also the subform on Batch (qryBatchIngredients) is not getting the records added to it. I need clarification on relationship for the query. I have 3 tables. BatchIngredients SemiProdIngredients RawMaterial BatchINGID(P) ProductIngredientId(P) Code(P) BatchId Semicode productname productIngredientId Code vendor# code Qtyper100lb(fraction) lot# semicode I got rid of the multiple vendors for this applications to hopefully simplify it for now. Still not clear on the query??? and why subform based on it is not getting populated. Thanks again for you help, barb "Michel Walsh" wrote: Hi, instead of ---------------- 'Assign SQL string to the string variable strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM Semiprodingredients" & "WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));" 'Execute the query DoCmd.RunSQL strSQL -------------- something like ---------------- 'Assign SQL string to the string variable strSQL = "INSERT INTO BatchIngredients(code,BatchID) " & " SELECT Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & " FROM Semiprodingredients" & " WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));" Debug.Print strSQL Debug.Assert False 'Execute the query DoCmd.RunSQL strSQL ------------------- I also add a Debug.Assert False so the code will stop just right after the strSQLvariable would have print its content in the debug immediate window. Note that I also assumed the strSQL= ... line is just in one line (the newsreader may have cut it into multiple lines). Hoping it may help, Vanderghast, Access MVP "babs" wrote in message news Thanks for the advice. Not sure where to put the Debug.Print SQL exactly. I took out the concatenation operators and added spacing but now getting syntax errors. Not sure what or where EXACTLY I need to add spacing. Please keep helping. thanks, Barb "Michel Walsh" wrote: Hi, add space around the keywords. If you debug.print your strSQL: Debug.Print strSQL after you filled it, and before you used it in your DoCmd statement, you should see something like: .... AS Expr1FROM Semi.... while you probably intend ... AS Expr1 FROM Semi... Your statement is read as is you want the alias name EXPR1FROM ... and no FROM clause if found. Same pattern with the SELECT (not critical since preceded by a closing parenthesis) and WHERE (for which it is critical) keywords. Add at least a SPACE before and after the keywords, for safety, add two, so you will immediately spot them, when you build the statement, through strings concatenation. The concatenation operator, &, DOES NOT do it for you. Hoping it may help, Vanderghast, Access MVP "babs" wrote in message ... Posted this again today since didn't here back sorry - A little anxious to get this figured out. Thanks SOOO Much for responding back. I followed your new directions and did all except 1st of all when I run the qrybatchIngredients on its own - I get no records??? also when I attach the VB code the command button I get an error- A dialog box pops up that says: Query Input must contain at least one Table or Query. then I have to click on okay. and records in BatchIngredients subform don't get populate. Below is my VB code: Private Sub Command25_Click() On Error GoTo Err_Command25_Click Dim strSQL As String 'Assign SQL string to the string variable strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM Semiprodingredients" & "WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));" 'Execute the query DoCmd.RunSQL strSQL 'Requery the subform to display the new records Me![BatchIngredients].Requery Exit_Command25_Click: Exit Sub Err_Command25_Click: MsgBox Err.Description Resume Exit_Command25_Click End Sub code=Ingredientscode Batch=Batches semiprodingredients=ProductIngredient semicode=ProductID not sure what cboProduct is = assumed it is the name of the my combo box on the Main Batches Form. Also not sure about the spacing at the end of your lines- don't think I needed the _ after the & signs?????? also not sure what name should go in the last line of code is it the Table name BatchIngredient???? or should it be the subform name. I tried that and got the same error. One other issue I have is that the Ingredients table has two primary keys - code and vendor, since one raw material can come from more than one vendor. Really just want the Batch ingredients to be populated with the raw mat. code for a given Semiproduct. the lot # or (Batch#) will indicate which vendor it came from. Hope you can help with getting this subform populated!!!!!! Supposedly needed this done by today. Oh Well THANKS SO MUCH AGAIN!!! Barb Assumed cbosemicode is the NAME of the combo box for "Sprinks" wrote: Hi, Babs. Yes. Quantity is a property of the batch because it is associated with the product. You'll calculate the quantities of the batch ingredients from it and the related fractions stored in ProductIngredients. As far as inserting records, assign code to a command button on your main Batches form to execute an Insert query. Not being a skilled SQL jockey, I prefer to create queries in Design View. Once saved, you can execute them with the OpenQuery method, which the Command Button wizard will do for you. Alternatively, you can switch to SQL view from query design view, and cut and paste the query's SQL into your command button procedure, and use the RunSQL method of the DoCmd object. Before we get to that, though, it occurred to me that you need the fraction field from ProductIngredients to calculate the ingredient quantities, and the Ingredient field from Ingredients to display the name of the ingredient. Pick them up with a query. Define a new query, named, say, qryBatchIngredients, based on the BatchIngredients, Ingredients, and ProductIngredients tables. Be sure relationships are established between BatchIngredient.IngredientID and Ingredients.IngredientID and between Ingredients.IngredientID and ProductIngredient.IngredientID. Drag all the BatchIngredient fields to the query grid, and also Ingredients.Ingredient (the text field not the numeric code) and ProductIngredients.Fraction. After you've saved the query, load your BatchIngredients subform in Design View and change the form's RecordSource from the table BatchIngredients to your new query. Place the controls you need. Remember you don't need or want the BatchIngredientID or the BatchID on your subform. To display the quantity needed for each ingredient, add a textbox to your subform and set its Control Source property to: =[Parent]![Qty]*[Fraction] Now for inserting the records. Create a new command button. Start it with Wizards enabled so that it will create the shell for you. Select any of the options, and then edit it and delete all the action code, but preserve the error-handling code. Then add the following: Dim strSQL As String ' Assign SQL string to the string variable strSQL = "INSERT INTO BatchIngredient ( IngredientID, BatchID) " & _ "SELECT ProductIngredient.IngredientID, Forms!Batches!BatchID AS Expr1 " & _ "FROM ProductIngredient " & _ "WHERE (((ProductIngredient.ProductID)=[Forms]![Batches]![cboProduct]));" ' Execute the query DoCmd.RunSQL strSQL ' Requery the subform to display the new records Me![BatchIngredient].Requery If you study the code, you'll see you pick up the BatchID (the linking field for the form/subform) from the main form, and the IngredientID's from ProductIngredient, using the form's ProductID to select the records. Your forms and fields may be named differently so you may need to edit this statement. Be careful to include a space at the end of each line except the last (within the quotes) or the string won't be valid SQL. Finally, the last statement requeries the subform so that it can display the new records. Since the only field you'll need your operators to edit is the ingredient's Batch Number, you can set the other controls' Enabled property to No or Locked property to Yes. I hope that solves it, but if you have any other questions, feel free to ask them. Sprinks "babs" wrote: Told coworker I would attemp to help her get this figured out. Please help 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 "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 |
#2
|
|||
|
|||
Hi, Babs.
If Semicode is the PK for the Products table, what I've been calling the ProductID, it does need to be, nor *should* it be stored in the BatchIngredients table. The tables are 3N-normalized as they are. You don't need this field because BatchIngredients is linked to Batches by the BatchID, and Batches is linked to Products by the ProductID (Semicode). Remove it entirely. If you wish Product fields to appear on a form, or print on a report, create a multi-table query and base your form/report on it. duplicate records. Yes, any well-behaving application shouldn't create duplicate records. The best way to handle this I think is to create a multi-field (No Duplicates) index on BatchIngredients. First delete any unwanted duplicates you've created already. You won't be able to do this from your form directly, since the subform is based on a non-updatable query. You could either work with the table directly, or copy your BatchIngredients form to a new name, delete the controls that display fields that are not in BatchIngredients, change the form's RecordSource to BatchIngredients rather than the query, and insert it onto another copy of the main form under a new name. Or you could work with the table directly (be careful). Once that is done, open the BatchIngredients table in Design view and click View, Indexes. It should show the Primary Key index that is already being maintained. Below it, enter a name for the new index, enter BatchID in the FieldName column, and retain the Ascending default. On the next line, leave the IndexName column blank, and enter IngredientID in the FieldName column. Return to the BatchID row, and in the Property list, change the value of Unique to "Yes". Save the table. Your user will now get a message saying the new rows cannot be added should he/she attempt to push the button twice. You should be aware that as many responders, including me, use the "Notify me of replies" feature of this website, that you will get much more timely responses if you respond to a previous reply rather than starting a new thread. In fact, the Newsgroup's Guidelines ask the same, so that any potential respondant has the history of the thread. Other tips are suggested in the Newsgroup's guidelines. Hope that solves your issues. Sprinks "babs" wrote: Here is a little more info. just messed with the command button that inserts the records and was on a record on the main form batch where I already populated or inserted the ingredients that go into the product on the subform. when i pressed the command button again it inserted a whole additional 20 records basically duplicates without the lots# put in. How could I safe guard this so the operators can make this happen. thanks so much, barb Below was what was posted before. ITS WORKING SO FAR SO GOOD. AS I DEVELOP MORE I AM SURE I WILL HAVE MORE QUESTIONS. CAN I POST THEM ATTN. SPRINKS!!!! THANKS A TON - NOW I CAN GET SOME OTHER STUFF DONE!!! "babs" wrote: The SQL worked however it is inserting the records into the BatchIngredients table which is what we want to do. The semicode is not inserted - don't we want that in there. Also the subform on Batch (qryBatchIngredients) is not getting the records added to it. I need clarification on relationship for the query. I have 3 tables. BatchIngredients SemiProdIngredients RawMaterial BatchINGID(P) ProductIngredientId(P) Code(P) BatchId Semicode productname productIngredientId Code vendor# code Qtyper100lb(fraction) lot# semicode I got rid of the multiple vendors for this applications to hopefully simplify it for now. Still not clear on the query??? and why subform based on it is not getting populated. Thanks again for you help, barb "Michel Walsh" wrote: Hi, instead of ---------------- 'Assign SQL string to the string variable strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM Semiprodingredients" & "WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));" 'Execute the query DoCmd.RunSQL strSQL -------------- something like ---------------- 'Assign SQL string to the string variable strSQL = "INSERT INTO BatchIngredients(code,BatchID) " & " SELECT Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & " FROM Semiprodingredients" & " WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));" Debug.Print strSQL Debug.Assert False 'Execute the query DoCmd.RunSQL strSQL ------------------- I also add a Debug.Assert False so the code will stop just right after the strSQLvariable would have print its content in the debug immediate window. Note that I also assumed the strSQL= ... line is just in one line (the newsreader may have cut it into multiple lines). Hoping it may help, Vanderghast, Access MVP "babs" wrote in message news Thanks for the advice. Not sure where to put the Debug.Print SQL exactly. I took out the concatenation operators and added spacing but now getting syntax errors. Not sure what or where EXACTLY I need to add spacing. Please keep helping. thanks, Barb "Michel Walsh" wrote: Hi, add space around the keywords. If you debug.print your strSQL: Debug.Print strSQL after you filled it, and before you used it in your DoCmd statement, you should see something like: .... AS Expr1FROM Semi.... while you probably intend ... AS Expr1 FROM Semi... Your statement is read as is you want the alias name EXPR1FROM ... and no FROM clause if found. Same pattern with the SELECT (not critical since preceded by a closing parenthesis) and WHERE (for which it is critical) keywords. Add at least a SPACE before and after the keywords, for safety, add two, so you will immediately spot them, when you build the statement, through strings concatenation. The concatenation operator, &, DOES NOT do it for you. Hoping it may help, Vanderghast, Access MVP "babs" wrote in message ... Posted this again today since didn't here back sorry - A little anxious to get this figured out. Thanks SOOO Much for responding back. I followed your new directions and did all except 1st of all when I run the qrybatchIngredients on its own - I get no records??? also when I attach the VB code the command button I get an error- A dialog box pops up that says: Query Input must contain at least one Table or Query. then I have to click on okay. and records in BatchIngredients subform don't get populate. Below is my VB code: Private Sub Command25_Click() On Error GoTo Err_Command25_Click Dim strSQL As String 'Assign SQL string to the string variable strSQL = "INSERT INTO BatchIngredients(code,BatchID)" & "SELECT Semiprodingredients.code,Forms!batch!BatchID AS Expr1" & "FROM Semiprodingredients" & "WHERE(((Semiprodingredients.semicode)=[Forms]![Batch]![cbosemicode]));" 'Execute the query DoCmd.RunSQL strSQL 'Requery the subform to display the new records Me![BatchIngredients].Requery Exit_Command25_Click: Exit Sub Err_Command25_Click: MsgBox Err.Description Resume Exit_Command25_Click End Sub code=Ingredientscode Batch=Batches semiprodingredients=ProductIngredient semicode=ProductID not sure what cboProduct is = assumed it is the name of the my combo box on the Main Batches Form. Also not sure about the spacing at the end of your lines- don't think I needed the _ after the & signs?????? also not sure what name should go in the last line of code is it the Table name BatchIngredient???? or should it be the subform name. I tried that and got the same error. One other issue I have is that the Ingredients table has two primary keys - code and vendor, since one raw material can come from more than one vendor. Really just want the Batch ingredients to be populated with the raw mat. code for a given Semiproduct. the lot # or (Batch#) will indicate which vendor it came from. Hope you can help with getting this subform populated!!!!!! Supposedly needed this done by today. Oh Well THANKS SO MUCH AGAIN!!! Barb Assumed cbosemicode is the NAME of the combo box for "Sprinks" wrote: Hi, Babs. Yes. Quantity is a property of the batch because it is associated with the product. You'll calculate the quantities of the batch ingredients from it and the related fractions stored in ProductIngredients. As far as inserting records, assign code to a command button on your main Batches form to execute an Insert query. Not being a skilled SQL jockey, I prefer to create queries in Design View. Once saved, you can execute them with the OpenQuery method, which the Command Button wizard will do for you. Alternatively, you can switch to SQL view from query design view, and cut and paste the query's SQL into your command button procedure, and use the RunSQL method of the DoCmd object. Before we get to that, though, it occurred to me that you need the fraction field from ProductIngredients to calculate the ingredient quantities, and the Ingredient field from Ingredients to display the name of the ingredient. Pick them up with a query. Define a new query, named, say, qryBatchIngredients, based on the BatchIngredients, Ingredients, and ProductIngredients tables. Be sure relationships are established between BatchIngredient.IngredientID and Ingredients.IngredientID and between Ingredients.IngredientID and ProductIngredient.IngredientID. Drag all the BatchIngredient fields to the query grid, and also Ingredients.Ingredient (the text field not the numeric code) and ProductIngredients.Fraction. After you've saved the query, load your BatchIngredients subform in Design View and change the form's RecordSource from the table BatchIngredients to your new query. Place the controls you need. Remember you don't need or want the BatchIngredientID or the BatchID on your subform. To display the quantity needed for each ingredient, add a textbox to your subform and set its Control Source property to: =[Parent]![Qty]*[Fraction] Now for inserting the records. Create a new command button. Start it with Wizards enabled so that it will create the shell for you. Select any of the options, and then edit it and delete all the action code, but preserve the error-handling code. Then add the following: Dim strSQL As String ' Assign SQL string to the string variable strSQL = "INSERT INTO BatchIngredient ( IngredientID, BatchID) " & _ "SELECT ProductIngredient.IngredientID, Forms!Batches!BatchID AS Expr1 " & _ "FROM ProductIngredient " & _ "WHERE (((ProductIngredient.ProductID)=[Forms]![Batches]![cboProduct]));" ' Execute the query DoCmd.RunSQL strSQL ' Requery the subform to display the new records Me![BatchIngredient].Requery If you study the code, you'll see you pick up the BatchID (the linking field for the form/subform) from the main form, and the IngredientID's from ProductIngredient, using the form's ProductID to select the records. Your forms and fields may be named differently so you may need to edit this statement. Be careful to include a space at the end of each line except the last (within the quotes) or the string won't be valid SQL. Finally, the last statement requeries the subform so that it can display the new records. Since the only field you'll need your operators to edit is the ingredient's Batch Number, you can set the other controls' Enabled property to No or Locked property to Yes. I hope that solves it, but if you have any other questions, feel free to ask them. Sprinks "babs" wrote: Told coworker I would attemp to help her get this figured out. Please help 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 "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 |
Duplicate records in query | C Tate | Running & Setting Up Queries | 3 | September 16th, 2004 09:36 PM |
New records can't be seen | rleblanc | Using Forms | 6 | August 14th, 2004 02:43 PM |
Cannot Insert New Records | Dorci | Using Forms | 1 | August 12th, 2004 06:31 PM |
Finding Duplicate Records | Running & Setting Up Queries | 1 | July 13th, 2004 03:50 AM | |
Eliminating duplicate records in consecutive rows of a column | Larry | Worksheet Functions | 1 | June 26th, 2004 01:42 AM |