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
|
|||
|
|||
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 |
#2
|
|||
|
|||
Revision Tracking
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 |
#4
|
|||
|
|||
Revision Tracking
"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 |
#5
|
|||
|
|||
Revision Tracking
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 |
#6
|
|||
|
|||
Revision Tracking
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 |
Thread Tools | |
Display Modes | |
|
|