Results 1 to 2 of 2
  1. #1
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82

    History of updates report/export?

    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.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't think you need to have a separate table, you can just sequence each part you substitute as you update your table. so item P2 would be sequence number 1, item R2 would be sequence number 2, item RR3 would be sequence number 3

    You just need to devise a way to count the existing replacements for each item before adding a replacement item. You could do this via the dlookup or dcount function (dlookup might be more effective) to return the current sequence number before adding a new record.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cell History (edit history)
    By andy.101 in forum Access
    Replies: 3
    Last Post: 09-27-2017, 10:25 AM
  2. Problem carrying out updates based on choices on a report
    By Zippy_The_Wonderdog in forum Reports
    Replies: 2
    Last Post: 03-21-2016, 02:56 PM
  3. Replies: 1
    Last Post: 08-10-2015, 02:10 PM
  4. Replies: 2
    Last Post: 02-25-2013, 10:08 PM
  5. Customer History Tabular Report
    By avarusbrightfyre in forum Reports
    Replies: 0
    Last Post: 03-02-2011, 04:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums