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
|
|||
|
|||
Please help
Here's my quandary:
In our research facility, processing of product involves several steps. The first 2 steps are the same for all product. I capture that information in 2 tables; tblComposition and tblManufacturing. TblComposition has fields for LotID, Component, and Amount of Component (a LotId can have 1-8 components). TblManufacturing has several fields including LotID, MfgEquip, Operator, etc. Some Lots are split out at this point to "child" Lots and receive additional processing, tblPostProcessing with fields LotID,ParentID,PPEquip, etc.. Both parent and child go on to get tested and assembled. When I run queries on the end product I need to know the Composition and Manufacturing details so to simplify the process I thought it best to copy that information from parent to child lot. An append query or 2 seems like a good option but I need a way for users to execute the queries perhaps from the form when the child Lot gets created and postprocessed. Any ideas on this or a better method to accomplish what I need? Thanks for the help. |
#2
|
|||
|
|||
Please help
Sounds more like a process design rather than database
design where the database needs to mimic the process - whichever process ends up being the most effective. The only thing I came up with while reading your puzzle was that I would favor the parent table having only the attributes set that applied before the lot was split out - plus I would have a Boolean field for setting a split flag. Then, I would have a new intersection table that lists the primary key of the parent and primary key of a child - one set per row for each new child. So if a lot makes two new sublots - then I would have two new lots in the main table with this intersection table having two rows to link each child back to momma. I would also do as you hinted, by having the process that creates the split be the moment in time that the new records are created, parent data copied to each child, and the entries in the interesection table added as a complete transaction that could be rolled back. This would permit a few good things. 1) Child lots would be in the main table 2) Parent lots could be divided into any number of child lots 3) Child lots could become parent lots and have their own offspring 4) The intersection table could always be consulted to find any lineage 5) Any report/query could be limited to only those records that have no offspring - keeping track of only final processed lots Just some ideas - hope they help. -----Original Message----- Here's my quandary: In our research facility, processing of product involves several steps. The first 2 steps are the same for all product. I capture that information in 2 tables; tblComposition and tblManufacturing. TblComposition has fields for LotID, Component, and Amount of Component (a LotId can have 1-8 components). TblManufacturing has several fields including LotID, MfgEquip, Operator, etc. Some Lots are split out at this point to "child" Lots and receive additional processing, tblPostProcessing with fields LotID,ParentID,PPEquip, etc.. Both parent and child go on to get tested and assembled. When I run queries on the end product I need to know the Composition and Manufacturing details so to simplify the process I thought it best to copy that information from parent to child lot. An append query or 2 seems like a good option but I need a way for users to execute the queries perhaps from the form when the child Lot gets created and postprocessed. Any ideas on this or a better method to accomplish what I need? Thanks for the help. . |
#3
|
|||
|
|||
Please help
Thanks Wayne. I was thinking that the ParentID field for
the child would provide the link for lineage. Can you explain the Split Flag. The primary key in both of my tables is SampleID so I don't know how to setup the intersection table. Bad design? I'm fairly new to this level of complexity so I'm having trouble taking ideas to code. Thanks for any details you can provide. -----Original Message----- Sounds more like a process design rather than database design where the database needs to mimic the process - whichever process ends up being the most effective. The only thing I came up with while reading your puzzle was that I would favor the parent table having only the attributes set that applied before the lot was split out - plus I would have a Boolean field for setting a split flag. Then, I would have a new intersection table that lists the primary key of the parent and primary key of a child - one set per row for each new child. So if a lot makes two new sublots - then I would have two new lots in the main table with this intersection table having two rows to link each child back to momma. I would also do as you hinted, by having the process that creates the split be the moment in time that the new records are created, parent data copied to each child, and the entries in the interesection table added as a complete transaction that could be rolled back. This would permit a few good things. 1) Child lots would be in the main table 2) Parent lots could be divided into any number of child lots 3) Child lots could become parent lots and have their own offspring 4) The intersection table could always be consulted to find any lineage 5) Any report/query could be limited to only those records that have no offspring - keeping track of only final processed lots Just some ideas - hope they help. -----Original Message----- Here's my quandary: In our research facility, processing of product involves several steps. The first 2 steps are the same for all product. I capture that information in 2 tables; tblComposition and tblManufacturing. TblComposition has fields for LotID, Component, and Amount of Component (a LotId can have 1-8 components). TblManufacturing has several fields including LotID, MfgEquip, Operator, etc. Some Lots are split out at this point to "child" Lots and receive additional processing, tblPostProcessing with fields LotID,ParentID,PPEquip, etc.. Both parent and child go on to get tested and assembled. When I run queries on the end product I need to know the Composition and Manufacturing details so to simplify the process I thought it best to copy that information from parent to child lot. An append query or 2 seems like a good option but I need a way for users to execute the queries perhaps from the form when the child Lot gets created and postprocessed. Any ideas on this or a better method to accomplish what I need? Thanks for the help. . . |
#4
|
|||
|
|||
Please help
Your idea sounded like having a seperate table for the
split lots. In that new (children only) table having a field for ParentID to link back to momma. I was looking at flipping this concept inside out or upside down - just the opposite, kind of. Instead of a seperate table for the two types of lots (those that are parents and those that are children) have one table for all lots. Add a new field in that one table of the yes/no (bit or Boolean) type where all unsplits are no and splits are yes. Create a new table that has only two fields for each record. The two fields would be parent and child ID numbers. This new table would just be a heritage table to locate who begot whom. The purpose of the true/false field in your main table is so that you can query the table for only children or parents, depending upon what you are wanting to know. If every lot is divided into exactly two, your main table would be 3 times larger than the incomming number of lots entered. eg. inbound 20 lots, all 20 split, 40 new children lots = total of 60 lots in the main table. The second (lineage) table would then have (with this example) 40 rows, or two rows per unique parent ID in the main table. -----Original Message----- Thanks Wayne. I was thinking that the ParentID field for the child would provide the link for lineage. Can you explain the Split Flag. The primary key in both of my tables is SampleID so I don't know how to setup the intersection table. Bad design? I'm fairly new to this level of complexity so I'm having trouble taking ideas to code. Thanks for any details you can provide. |
Thread Tools | |
Display Modes | |
|
|