Average tenure calc

[mike.godfrey]

I was wondering if anyone can tell me how to calculate average tenure
in MS Access? I am trying to calculate the average length of a
Maintenance Agreement customer for my company. For example:
Customer 1 signed up on 1/1/2002
Customer 2 signed up on 1/1/2003
Customer 3 signed up on 1/1/2004.

Todays date is 4/14/2007. So in this case the average tenure would be
4 years, 4 months.

 

[Guest]

Hi Mike,

You can use the following query:

SELECT Avg((Date()-[DateSignedUp])*12/364.25) AS AvgTenure
FROM tblCustomers;

---> Returns 51.5 months (as of Apr. 14, 2007)

This is very close to the 52 months you indicated. This SQL statement
assumes a field name of "DateSignedUp" in a table named "tblCustomers". Make
the appropriate substitutions in your case.

You can also use the DateDiff function, however, this function returns a
long integer, so you won't get fractional months:

SELECT Avg(DateDiff("m",[DateSignedUp],Date())) AS AvgTenure
FROM tblCustomers;

---> Returns 51 months (as of Apr. 14, 2007).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[mike.godfrey]

Thanks for the response Tom.
I think I got the result I expected. Now to take it a step further,
how would i modify it so the result would say "2 years, 6 months"
instead of 30.xxx?

Thanks again.
mike

 

[Guest]

Hi Mike,

I believe Access MVP Doug Steele has just the function for you!

A More Complete DateDiff Function
http://www.accessmvp.com/djsteele/Diff2Dates.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Okay, I replied too quickly. You could copy & paste the code shown on Doug's
site into the new module, and then create the following query:

SELECT Diff2Dates("ym",[DateSignedUp],Date()) AS ElapsedTime
FROM tblCustomers;

This query returns the following results, one result for each customer (you
can add the customer name, as well):

ElapsedTime
5 years 3 months
4 years 3 months
3 years 3 months

However, you cannot group this result and calculate an average (Datatype
Mismatch error). So, it looks like you will need to create your own custom
function and feed it the results of the first query that I provided, in order
to convert it into a form indicated above. You might be able to use the
Diff2Dates function to gleam some ideas for writing your own custom function.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[mike.godfrey]

Thanks once again Tom. You were very helpful. I will do some digging
and see what i can find. Take care.

mike

 

[Guest]

Hi Mike,

One method that might work, without having to modify the existing Diff2Dates
function that is posted on Doug's site, is to do the following sequence:

1.) Calculate the average tenure length in days. You can use the built-in
DateDiff function, as long as you don't care if the result is an integer:

qryAvgTenure
SELECT Avg(DateDiff("d",[DateSignedUp],Date())) AS AvgTenure
FROM tblCustomers;

---> Returns 1565 (as of today, April 15, 2007)

2.) Using the above result, calculate a new (average) DateSignedUp value.
You can use the built-in DateAdd function.

AvgDateSignedUp
SELECT DateAdd("d",(-1)*[AvgTenure],Date()) AS AvgDateSignedUp
FROM qryAvgTenure;

---> Returns 1/1/2003 (as of today, April 15, 2007)

3.) Feed the result of #2, above, into the Diff2Dates function.

qryAvgTenureInWords
SELECT Diff2Dates("ym",[AvgDateSignedUp],Date(),True) AS AvgTenure
FROM qryAvgDateSignedUp;

---> Returns: 4 years 3 months (as of today, April 15, 2007)


Or, using "ymd" instead of "ym" as the first parameter:

SELECT Diff2Dates("ymd",[AvgDateSignedUp],Date(),True) AS AvgTenure
FROM qryAvgDateSignedUp;

---> Returns: 4 years 3 months 14 days (as of today, April 15, 2007)


That took three queries to get the result, without having to modify the
Diff2Dates function. However, it should be relatively easy to create a new
function that performs the first two calculations and then calls the
Diff2Dates function.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________