Calculating "maturity" with queries

[inescquadros]

Hi! I'm having trouble preparing a query (in order to make a report
chart). Here is the situation:

I have a table where I have each worker, it's admitance date and it's
firing date (Fields: ID; Name; AdmitDate;FiringDate). Some workers were
hired in previous years, others were hired sometime this year. On
firing dates there is a mixture, too: some were fired in previous
years, others, during this year and many of them are still at work.

I also have a filtered query (based on a table with 10 years of dates)
with months already passed this year (Jan-2006 to Sep-2006).

I need to calculate the average number of team's months old, in every
given month of this year (in Jan-2006; in Feb-2006, ..., in Sep-2006) -
so that I can observe if the team is aging or not (I use months because
of team's high rotation). How can I perform this calculation?

Thanks.

 

[Guest]

Try this --
SELECT Format([FiringDate],"yyyymm") AS [Month],
Avg(DateDiff("m",[AdmitDate],[FiringDate])) AS Maturity
FROM inescquadros
GROUP BY Format([FiringDate],"yyyymm");