Revision Tracking

Hello,
Using an A2k Database. Have a BOM table containing a primary key combined by
two columns - BOMID and RevNo. The RevNo column is to track revisions to the
BOM table. The system increments the RevNo column after each revision. The
BOMID column remains the same for all revisions.

The system works fine till I create child tables based on the BOM Table (For
eg. a Purchase Order Table). The child table references both BOMID and RevNo
as foreign keys. When a user revises a BOM the child record - referencing an
earlier revision - 'disappears' from reports. It does not stand to business
logic that a minor change in a BOM record (say, a change in Dwg Code which
does not affect the PO Table) means the Purchase Orders raised for the BOM
record become invalid and must be re-created or re-assigned the correct
foreign key.

Is there any way to work around this problem. What happens in Access is
technically correct but leads to practical difficulties.

TIA
Anand

On Sat, 13 Jun 2009 04:09:01 -0700, Anand wrote:

Hello,
Using an A2k Database. Have a BOM table containing a primary key
combined by two columns - BOMID and RevNo. The RevNo column is to track
revisions to the BOM table. The system increments the RevNo column after
each revision. The BOMID column remains the same for all revisions.

The system works fine till I create child tables based on the BOM Table
(For eg. a Purchase Order Table). The child table references both BOMID
and RevNo as foreign keys. When a user revises a BOM the child record -
referencing an earlier revision - 'disappears' from reports. It does not
stand to business logic that a minor change in a BOM record (say, a
change in Dwg Code which does not affect the PO Table) means the
Purchase Orders raised for the BOM record become invalid and must be
re-created or re-assigned the correct foreign key.

Is there any way to work around this problem. What happens in Access is
technically correct but leads to practical difficulties.

TIA
Anand

There is no requirement that the PK of one table must be the foreign key
in related tables. Just use the BOMID by itself for the relationship to
POs.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

You ought to consider changing the design of your tables to:
TblBOM
BOMID
etc

TblBOMRevision
BOMRevisionID
BOMID
RevisionNum
RevisionDate
RevisionDesc

You have a one-to-many relationship between BOM and revision.

Steve






"Anand" wrote in message
...
Hello,
Using an A2k Database. Have a BOM table containing a primary key combined
by
two columns - BOMID and RevNo. The RevNo column is to track revisions to
the
BOM table. The system increments the RevNo column after each revision. The
BOMID column remains the same for all revisions.

The system works fine till I create child tables based on the BOM Table
(For
eg. a Purchase Order Table). The child table references both BOMID and
RevNo
as foreign keys. When a user revises a BOM the child record - referencing
an
earlier revision - 'disappears' from reports. It does not stand to
business
logic that a minor change in a BOM record (say, a change in Dwg Code which
does not affect the PO Table) means the Purchase Orders raised for the BOM
record become invalid and must be re-created or re-assigned the correct
foreign key.

Is there any way to work around this problem. What happens in Access is
technically correct but leads to practical difficulties.

TIA
Anand

"Steve" help_available_at_very_reasonable_rates@contactme .com schreef in
bericht ...
You ought to consider changing the design of your tables to:

Steve: You ought to consider changing your attitude and behaviour here...

Arno R

Why not have a one-to-many from BOM to RevNo table? Then you would have n
problem of BOM to Orders.

"Anand" wrote:

Hello,
Using an A2k Database. Have a BOM table containing a primary key combined by
two columns - BOMID and RevNo. The RevNo column is to track revisions to the
BOM table. The system increments the RevNo column after each revision. The
BOMID column remains the same for all revisions.

The system works fine till I create child tables based on the BOM Table (For
eg. a Purchase Order Table). The child table references both BOMID and RevNo
as foreign keys. When a user revises a BOM the child record - referencing an
earlier revision - 'disappears' from reports. It does not stand to business
logic that a minor change in a BOM record (say, a change in Dwg Code which
does not affect the PO Table) means the Purchase Orders raised for the BOM
record become invalid and must be re-created or re-assigned the correct
foreign key.

Is there any way to work around this problem. What happens in Access is
technically correct but leads to practical difficulties.

TIA
Anand

Hello,
Thanks for the inputs. Will try it out. Still have some doubts about how
this will work out for further integration with other processes. Will check.

Thanks
Anand

"KARL DEWEY" wrote:

Why not have a one-to-many from BOM to RevNo table? Then you would have n
problem of BOM to Orders.

"Anand" wrote:

Hello,
Using an A2k Database. Have a BOM table containing a primary key combined by
two columns - BOMID and RevNo. The RevNo column is to track revisions to the
BOM table. The system increments the RevNo column after each revision. The
BOMID column remains the same for all revisions.

The system works fine till I create child tables based on the BOM Table (For
eg. a Purchase Order Table). The child table references both BOMID and RevNo
as foreign keys. When a user revises a BOM the child record - referencing an
earlier revision - 'disappears' from reports. It does not stand to business
logic that a minor change in a BOM record (say, a change in Dwg Code which
does not affect the PO Table) means the Purchase Orders raised for the BOM
record become invalid and must be re-created or re-assigned the correct
foreign key.

Is there any way to work around this problem. What happens in Access is
technically correct but leads to practical difficulties.

TIA
Anand