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.

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.

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.