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
|
|||
|
|||
Related records depend on other related records
I'm not getting my brain around this one. The general situation is that
machine parts are processed according to a Process Plan. A Process Plan may be for one part or several. Also, a part may be processed according to one of several Process Plans. These are the tables for this set-up: tblPlan PlanID (PK) InternalNumber tblPart PartID (PK) PartNumber PartDescription tblPlanPart (junction table) PlanID (FK) PartID (FK) (PK is both fields) Also, a Process Plan may be revised: tblRev RevLevel PlanID (FK) RevDate (RevLevel and PlanID are the combined PK) The database is an index of existing Process Plans, which are Word documents. After the document is revised, the database records are updated. The Process Plans themselves are not part of the database, and are not likely to be in the future. I am giving no consideration to that contingency. Here's the wrinkle: A revision may involve adding, removing, or changing Part Numbers. In maybe 90% of cases the Part Number listing is the same, so when a new revision is entered the Part Number listing from tblPlanPart is the same as for the previous revision. If the PartNumber listing changes it tends to be for just a few parts, so the Part Number listing from the previous revision is a good starting point for the new listing. BTW, even though each InternalNumber is unique, I am using a separate PK in tblPlan in case the numbering system changes. It has happened before. In terms of the interface, my thinking is that there will be a main form for the Process Plan, with a subform for Revision, and another subform for PlanPart. When viewing the record for a ProcessPlan at, say, Rev. A, the appropriate PartNumber listing will need to be shown. If a PartNumber was added for Rev. B, when viewing Rev. B the revised PartNumber listing will need to be shown. I understand that I need to start with the table design, not the interface. I explained the interface briefly in the hope that it may clarify the intent of the structure, *not* because I am designing forms and then trying to add tables. What I can't quite see is how to relate the PartNumber listing to the Rev level, since both are related to the main Plan record rather than to each other. I suppose I would store the Rev level with each record in the PlanPart listing. Or maybe tblPlanPart table needs to be related to tblRev rather than to the main record. In that case I could use Allen Browne's code to duplicate the main form record and the related subform records. It would be a subform rather than a main form, and a sub-subform rather than a subform, but that should not cause problems. I have used the code to good effect in other projects. After duplicating the PartNumber listing it could be edited as needed. I'm leaning to the approach where I would relate tblPlanPart to tblRev, but before heading down that path I would like to know whether anybody sees a more effective or simpler approach that I have missed. |
#2
|
|||
|
|||
Related records depend on other related records
Hi Bruce,
The thought of the top of my head would be to make the revision information part of the plan table. Basically each revision is a new, slightly different plan. PlanID RevisionID RevisionDate Make your primary key PlanID, RevisionID. For all revisions the PlanID would be the same. Alternatively you could make the plan/part table a child of the plan revision table instead of the plan table. Hope that helps, Clifford Bass "BruceM" wrote: I'm not getting my brain around this one. The general situation is that machine parts are processed according to a Process Plan. A Process Plan may be for one part or several. Also, a part may be processed according to one of several Process Plans. These are the tables for this set-up: tblPlan PlanID (PK) InternalNumber tblPart PartID (PK) PartNumber PartDescription tblPlanPart (junction table) PlanID (FK) PartID (FK) (PK is both fields) Also, a Process Plan may be revised: tblRev RevLevel PlanID (FK) RevDate (RevLevel and PlanID are the combined PK) The database is an index of existing Process Plans, which are Word documents. After the document is revised, the database records are updated. The Process Plans themselves are not part of the database, and are not likely to be in the future. I am giving no consideration to that contingency. Here's the wrinkle: A revision may involve adding, removing, or changing Part Numbers. In maybe 90% of cases the Part Number listing is the same, so when a new revision is entered the Part Number listing from tblPlanPart is the same as for the previous revision. If the PartNumber listing changes it tends to be for just a few parts, so the Part Number listing from the previous revision is a good starting point for the new listing. BTW, even though each InternalNumber is unique, I am using a separate PK in tblPlan in case the numbering system changes. It has happened before. In terms of the interface, my thinking is that there will be a main form for the Process Plan, with a subform for Revision, and another subform for PlanPart. When viewing the record for a ProcessPlan at, say, Rev. A, the appropriate PartNumber listing will need to be shown. If a PartNumber was added for Rev. B, when viewing Rev. B the revised PartNumber listing will need to be shown. I understand that I need to start with the table design, not the interface. I explained the interface briefly in the hope that it may clarify the intent of the structure, *not* because I am designing forms and then trying to add tables. What I can't quite see is how to relate the PartNumber listing to the Rev level, since both are related to the main Plan record rather than to each other. I suppose I would store the Rev level with each record in the PlanPart listing. Or maybe tblPlanPart table needs to be related to tblRev rather than to the main record. In that case I could use Allen Browne's code to duplicate the main form record and the related subform records. It would be a subform rather than a main form, and a sub-subform rather than a subform, but that should not cause problems. I have used the code to good effect in other projects. After duplicating the PartNumber listing it could be edited as needed. I'm leaning to the approach where I would relate tblPlanPart to tblRev, but before heading down that path I would like to know whether anybody sees a more effective or simpler approach that I have missed. |
#3
|
|||
|
|||
Related records depend on other related records
Sometimes in the course of writing a question I gain some insight or
understanding, or think of another angle to pursue. I doubt that I post half of the questions I start writing. In this case I was coming to the realization that I can think of the PlanPart listing as related to the Revision rather than to the ProcessPlan, but I had some doubts, so I went ahead and posted. I think it is a better choice to relate PlanPart to Revision than to include Revision with the ProcessPlan record in that there are other records related to ProcessPlan that do not change with the Revision level. Thanks for the feedback. "Clifford Bass" wrote in message ... Hi Bruce, The thought of the top of my head would be to make the revision information part of the plan table. Basically each revision is a new, slightly different plan. PlanID RevisionID RevisionDate Make your primary key PlanID, RevisionID. For all revisions the PlanID would be the same. Alternatively you could make the plan/part table a child of the plan revision table instead of the plan table. Hope that helps, Clifford Bass "BruceM" wrote: I'm not getting my brain around this one. The general situation is that machine parts are processed according to a Process Plan. A Process Plan may be for one part or several. Also, a part may be processed according to one of several Process Plans. These are the tables for this set-up: tblPlan PlanID (PK) InternalNumber tblPart PartID (PK) PartNumber PartDescription tblPlanPart (junction table) PlanID (FK) PartID (FK) (PK is both fields) Also, a Process Plan may be revised: tblRev RevLevel PlanID (FK) RevDate (RevLevel and PlanID are the combined PK) The database is an index of existing Process Plans, which are Word documents. After the document is revised, the database records are updated. The Process Plans themselves are not part of the database, and are not likely to be in the future. I am giving no consideration to that contingency. Here's the wrinkle: A revision may involve adding, removing, or changing Part Numbers. In maybe 90% of cases the Part Number listing is the same, so when a new revision is entered the Part Number listing from tblPlanPart is the same as for the previous revision. If the PartNumber listing changes it tends to be for just a few parts, so the Part Number listing from the previous revision is a good starting point for the new listing. BTW, even though each InternalNumber is unique, I am using a separate PK in tblPlan in case the numbering system changes. It has happened before. In terms of the interface, my thinking is that there will be a main form for the Process Plan, with a subform for Revision, and another subform for PlanPart. When viewing the record for a ProcessPlan at, say, Rev. A, the appropriate PartNumber listing will need to be shown. If a PartNumber was added for Rev. B, when viewing Rev. B the revised PartNumber listing will need to be shown. I understand that I need to start with the table design, not the interface. I explained the interface briefly in the hope that it may clarify the intent of the structure, *not* because I am designing forms and then trying to add tables. What I can't quite see is how to relate the PartNumber listing to the Rev level, since both are related to the main Plan record rather than to each other. I suppose I would store the Rev level with each record in the PlanPart listing. Or maybe tblPlanPart table needs to be related to tblRev rather than to the main record. In that case I could use Allen Browne's code to duplicate the main form record and the related subform records. It would be a subform rather than a main form, and a sub-subform rather than a subform, but that should not cause problems. I have used the code to good effect in other projects. After duplicating the PartNumber listing it could be edited as needed. I'm leaning to the approach where I would relate tblPlanPart to tblRev, but before heading down that path I would like to know whether anybody sees a more effective or simpler approach that I have missed. |
Thread Tools | |
Display Modes | |
|
|