I am creating a database for the purpose of calculating the length of time that had passed between two milestones.
[ID] [Milestone] [MDate] [Length of Time]
--------------------------------------------------------
1 1 Jan 1, 2013 [If the following milestone is 2 and they have both the same ID; Calculate Datediff of Jan 1, 2013 vs Feb 1, 2013] Ans: 1 month]
1 2 Feb 1, 2013 [if no milestone that followed, end date should be date(); Datediff= Feb 1, 2013 vs. Date()]
2 1 Jan 15, 2013 [If the following milestone is 2 and they have both the same ID; Calculate Datediff of Jan 15, 2013 vs Feb 1, 2013] Ans: 18 days]
2 2 Feb 1, 2013 [If the following milestone is 3 and they have both the same ID; Calculate Datediff of Feb 1, 2013 vs Feb 27, 2013] Ans: 12 days]
2 3 Feb 27, 2013 if no milestone that followed, end date should be date(); Datediff= Feb 27, 2013 vs. Date()]
The setup of the table is:
[ID] - Number;
[Milestone] - Number;
[Mdate]-Date;
[Length of time] - calculated field in the query; should be with a user defined function
The problem is I cannot automate the calculation of length of time between the two. I need my database to sense
that when two records with the same ID and a milestone number was changed, the previous milestone will use the end
date of the following milestone, else if nothing follows the end date is current date.
I need a user defined function that automatically calcualate the length of time.