I am putting together a database to keep track of material lists for specific projects. Basicly we have any number of projects and each project has its own list of materials. the database will allow reviewers to change specific parts if it is needed (ie replace bolt for stainless steel bolt). one of the requirements is a report that clearly shows the audit trail from the initial list to the final approved list. I need to build it to handle any number of replacements as a specific item may change 2 or 3 or more times in the review process.
EXAMPLE:
Initial Parts List 1
P1
P2
P3
Parts List 1 Changes
P1
P2 > replaced by R2 > replaced by RR3
P3 > replaced by AA1
Final Parts List 1
P1
RR3
AA1
I need a report or export that shows:
Parts List 1
P1
RR3
Replaces R2 [by John D on 8/15/19 - Reason: Updated version]
Replaces P2 [by Sam S on 8/12/19 - Reason: Availability]
AA1
Replaces P3 [by Sam S on 8/12/19 - Reason: Size change]
I currently have the following tables:
tblParts
ID
Part#
Qty
Replaced (y/n)
Replaced_By_ID (ID#)
tblHistory
Original_ID
New_ID
User_ID
Date
Reason
I know that I am duplicating data with Replaced_By_ID and the tblHistory
Any thoughts/tips/advise on how to get to the report in blue above would be GREATLY appreciated.