Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2011
    Posts
    27

    Unanswered: Tracking Due Date Changes

    Hello,

    I can't seem to find an elegant (or decent) solution to a problem I have come across.

    I want to track when project DueDates are changed. If a report is due on 5/8, and then it is changed to 5/10, I want to be able to see that the report's due date was changed from one to the other on 4/10. Reports would use the last function to show the latest DueDate, but I also need to mine data to see how much timelines shift.

    To get an idea of what I'm trying to do, I have a half-baked table:

    JunctionProjectDeliverableTypesID
    ProjectsFK (project deliverable is associated)
    DeliverableTypesFK (type of report, about 5 different types)
    DateDue
    EditDate (date that user changed DateDue)
    ChangeDate (date that client shifted DateDue, if client changed DueDate on 5/5 and data was entered 5/7, this would be 5/5 and EditDate would be 5/7)
    User (user that recorded record)

    I prefer to use minimal VBA to keep things simple, but it's not a problem if I have to write code.

    I tried an audit tracking table that records changes. I almost got it to record original entries too, but I would prefer to avoid this since it would be more difficult to create entry forms. I think that a way of tracking the change order would be ideal. Initial entries get a 1, the first change gets a 2, and on; but I'm not figuring it out.

    Any ideas? Thanks!

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    well there is more than 1 way to skin this cat.

    I might add a new record to the table, and to the form but not visible...labeled 'PriorDueDate'......and then in the BeforeUpdate event of DueDate put in a line of code that writes its value to this new PriorDueDate before it is overwritten. So you have the old date recorded.

    I would add a second new field to table/form not visible that is ChangeTimeStamp and in the same event simply write in Now(). The purpose of this is to help you find recent changes - - you can simply query on this field to review changes occuring within a defined time period.

    Hope it helps.
    www CahabaData com

  3. #3
    Join Date
    Jan 2011
    Posts
    27
    I made example data to see how I would want to analyze it. Query output would look something like this:

    Code:
    ProjectFK DeliverableID	DeliverableTypeFK DueDate	EditDate	User
    Project 1	1	Interim Report	4/16/2011	3/1/2011	User 1
    Project 1	2	Final Report	5/8/2011	3/1/2011	User 1
    Project 1	2	Final Report	5/10/2011	4/10/2011	User 1
    Project 1	2	Final Report	5/15/2011	4/25/2011	User 1
    Project 2	3	Interim Report	4/10/2011	3/10/2011	User 1
    Project 2	3	Interim Report	4/15/2011	4/1/2011	User 1
    Project 2	3	Interim Report	4/16/2011	4/12/2011	User 1
    Project 2	4	Final Report	5/10/2011	3/10/2011	User 1
    Project 2	4	Final Report	5/20/2011	4/1/2011	User 2
    Project 2	4	Final Report	5/25/2011	5/15/2011	User 2
    Thanks, NTC. That is very much like the audit code I had. Is there another way to skin this cat without injecting all the data into another table? And to include the original value? It's not so much for monitoring changes as it is to mine data.

    I think I can make a junction table that includes the first 3 fields:

    JunctionProjectDeliverableTypesID
    ProjectsFK (project deliverable is associated)
    DeliverableTypesFK (type of report, about 5 different types)

    and then another junction table (depending on if dates are normalized) that looks like:

    Table: DeliverableTimeline (almost JunctionJunctionProjectDeliverableTypesDates if I make a Dates table as an FK)

    DeliverableTimelineID (ID to find most recent, would prefer 1,2,3 for each project, but oh well)
    JunctionProjectDeliverableTypesFK (connects to a "Deliverable")
    DueDate
    EditDate
    UserFK
    Reason (reason for change)

    In the entry form, it will have a textbox as a controlsource that gets the project from a list. A continuous form for JunctionProjectDeliverableTypes will list the Deliverables for the project and the max(DueDate) to show the current due date. Choosing one of those will create a text box that allows a new date. Typing it will save all the current variables, the new date, and a new DeliverableTimelineID. I don't think I can avoid VBA for the username and now(), though I could display those in text boxes maybe.

    Does this sound like a good plan? Or is there something easier?

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    my first post was involving the same table, not another table. and did write the old due date to the new field before it gets overwritten in the duedate field....
    www CahabaData com

  5. #5
    Join Date
    Jan 2011
    Posts
    27
    Cool, NTC, I didn't realize that. Can you explain more? If I haven't mentioned earlier, I need the ability to record any number of changes to the same date. How do you hold that all in the same table? I guess I'm not understanding how your solution works yet.

  6. #6
    Join Date
    Oct 2009
    Posts
    340
    my first post was a simple method to record the old due date into another unseen field; plus a new timestamp field to aid you in doing a query to find what changes have occurred recently.

    that's what your original post seemed to need.

    my first post is simple and can be extended somewhat to multiple fields if one wishes; but inherently only holds one old change. if they make a second change then the new old data over writes the older old data....and for many cases this is fine....such as the change of a due date as you indicated.

    there is a different approach if you need to record multiple fields changes and at the same time also record all the history of all changes....in this case, which is more complicated - you really get into revision management - - and one makes brand new records each time and track their order.
    www CahabaData com

  7. #7
    Join Date
    Jan 2011
    Posts
    27
    I found a way to record dates the way I need it.

    JunctionProjectsDeliverableTypes Table

    JunctionProjectsDeliverableTypesID
    ProjectsFK
    DeliverableTypesFK

    DeliverableDueDate Table

    JunctionProjectsDeliverableTypesFK
    DueDate
    EditDate
    User

    I'm using a blank form with a projects list subform. It has the text box as a Master trick, so another subform has the filtered JunctionPDTypes. Clicking on a report type then is a master (another text box as a control trick) to the DeliverableDueDate subform that is in Data Entry mode to create only new records. When a DueDate is entered, it creates a new record and the rest is a default expression. EditDate is Now() and I wrote a three line function to get the user name to use in a default expression for User. The Junction form has an additional expression to show the DLast date for reference, the most recent.

    A simple query joins the two together into the form I need it in! I'm glad I explored expressions as a default and in text boxes. Somehow I managed without.

    Thanks for the help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •