First, I'm sorry for the lack of description in my title. I'm not 100% sure how to explain what I'm trying to accomplish. I've been asked to migrate a contract document tracking log from Excel to Access. I have the tables and forms working and they are currently being tested by my team. I am now in the process of creating reports to distribute to our Field Teams so we can update them on the status of their contract. The structure of the DB is as follows:
I have a table called "Contract_Table" which has the various action items that we need to track such as "Sent_Vendor", "Rec_frm_Vendor", "Sent_VP", "Rec_from_VP". We use dates to mark the accomplishment of these tasks. These are just a few sample fields within "Contract_Table". Below is the requests that I've received. I'm seeking advise on how (or if) I can accommodate their request.
Can I create a report that shows how long the contract has been on the current task? Example: "Sent_Vendor" = Isblank for x amount of days. Then it moves to a current status of "Rec_from_Vendor" and that has been isblank for x amount of days.
Any advice is greatly appreciated. I'm self-taught with very minimal/basic knowledge of VBS. I'm not sure if this can be handled by codes or if I need to create a separate "tracking" table. I do have a change table that tracks all edits and who made them.
There was another post asking something similar, so I am going to go back to the well. It looks like you are trying to determine status of your workflow based which field is filled in with a date. Assuming that your workflow operates serially (Sent_Vendor -> Rec_from_Vendor -> Sent_VP -> Rec_from_VP), you can functionally unpivot your table through a series of UNION ALL statements with the following criteria in SQL view of a query:
SELECT ContractID, "Sent_Vendor" AS [Status], DateDiff("d",[Sent_Vendor],Date()) AS [Time_Elapsed]
WHERE (((Sent_Vendor) Is Not Null) AND ((Rec_frm_Vendor) Is Null))
UNION ALL SELECT ContractID, "Rec_frm_Vendor" AS [Status], DateDiff("d",[Rec_frm_Vendor],Date()) AS [Time_Elapsed]
WHERE (((Rec_frm_Vendor) Is Not Null) AND ((Sent_VP) Is Null))
UNION ALL SELECT ContractID, "Sent_VP" AS [Status], DateDiff("d",[Sent_VP],Date()) AS [Time_Elapsed]
WHERE (((Sent_VP) Is Not Null) AND ((Rec_from_VP) Is Null));
With the assumption that a contract in a status of Sent_Vendor will have a populated date for that field, but an empty field for Rec_from_Vendor and so one through the workflow, you can isolate the status of any particular contract. The time elapsed field is a calculation based on the date from the current status off of today's date. Note, this report will not include results for anything "Completed" through the workflow (Rec_from_VP is not null) since this report does not seem geared for that result.