B
BruceM
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.
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.