User Defined Function for Calculating Length of Time

[buyoy]

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.

[Rabbit]

You don't need a user defined function. You can outer join the table to itself on the id and the next milestone. That will allow you to calculate the length on the fly.