Calculate years from multiple records

[Mariah]

I need to run a query and am running into a little trouble.

At this point the query includes the names of our members, the jobs they
have held, and the dates held. I need to add up the total number of years in
each job title by member.
The members may have held many jobs, some repetitive. Each job is entered as
a separate record in a table, with the job attached to the member via their
key.
I am wondering if there is a way to add up the number of years that a job
title was held for each member. We have about 600 different job titles at
this point.
This is an example of the members job history in the employment table:

NameLookup Job FromDate ToDate Location
Jane Smith Teacher 8/25/1999 5/15/2000 Rhodant Middle School
Jane Smith Teacher 8/31/2000 6/12/2001 ROUS Elementary
Jane Smith Teacher 8/16/2001 5/31/2002 Rhodant Middle School
Jane Smith Consultant 8/12/2002 7/1/2003 Self-Employed
Jane Smith Sabbatical 7/2/2003 9/5/2005 Redding, MT
Jane Smith Housekeeper 11/1/2005 12/2/2005 R.C.H. Hotel
Mary Doe Teacher 1/12/1975 1/12/1986 Rhodant Middle School
Mary Doe...

I found a way to count the number of years as teacher for each member but I
need to produce something that can count years in each position by member.
Do I need to create a separate query for each job title (600+)?
I need something like this on my report:

Jane Smith (years)
Teacher (4)
Consultant (1)
Sabbatical (2)
Housekeeper (1)
Mary Doe
Teacher (10)

This query will be added as a subreport to an existing report full of member
information that is sorted by celebration location. If I can get the query I
will be good to go!

Any help is very much appreciated.

 

[KARL DEWEY]

The problem is figuring what constitues a year. Here are a couple of ways to
compute but they give different answers --
SELECT Employment.NameLookup, Employment.Job,
Sum(DateDiff("yyyy",[FromDate],[ToDate])+1) AS Years
FROM Employment
GROUP BY Employment.NameLookup, Employment.Job;

SELECT Employment.NameLookup, Employment.Job,
Round(Sum(DateDiff("q",[FromDate],[ToDate])/4)) AS Years
FROM Employment
GROUP BY Employment.NameLookup, Employment.Job;


SELECT Employment.NameLookup, Employment.Job,
Sum(Round(DateDiff("q",[FromDate],[ToDate])/4)) AS Years
FROM Employment
GROUP BY Employment.NameLookup, Employment.Job;

 

[Mariah]

THANK YOU! You are awesome! Your solution solved the problem.

You are right, the answers do come out slightly different depending on which
code is used, and after a brief conference we choose the results which
everyone thought best reflected what we needed.

This will make life SO much simpler.

Thanks again!

The problem is figuring what constitues a year. Here are a couple of ways to
compute but they give different answers --
SELECT Employment.NameLookup, Employment.Job,
Sum(DateDiff("yyyy",[FromDate],[ToDate])+1) AS Years
FROM Employment
GROUP BY Employment.NameLookup, Employment.Job;

SELECT Employment.NameLookup, Employment.Job,
Round(Sum(DateDiff("q",[FromDate],[ToDate])/4)) AS Years
FROM Employment
GROUP BY Employment.NameLookup, Employment.Job;


SELECT Employment.NameLookup, Employment.Job,
Sum(Round(DateDiff("q",[FromDate],[ToDate])/4)) AS Years
FROM Employment
GROUP BY Employment.NameLookup, Employment.Job;

--
KARL DEWEY
Build a little - Test a little

I need to run a query and am running into a little trouble.

At this point the query includes the names of our members, the jobs they
have held, and the dates held. I need to add up the total number of years in
each job title by member.
The members may have held many jobs, some repetitive. Each job is entered as
a separate record in a table, with the job attached to the member via their
key.
I am wondering if there is a way to add up the number of years that a job
title was held for each member. We have about 600 different job titles at
this point.
This is an example of the members job history in the employment table:

NameLookup Job FromDate ToDate Location
Jane Smith Teacher 8/25/1999 5/15/2000 Rhodant Middle School
Jane Smith Teacher 8/31/2000 6/12/2001 ROUS Elementary
Jane Smith Teacher 8/16/2001 5/31/2002 Rhodant Middle School
Jane Smith Consultant 8/12/2002 7/1/2003 Self-Employed
Jane Smith Sabbatical 7/2/2003 9/5/2005 Redding, MT
Jane Smith Housekeeper 11/1/2005 12/2/2005 R.C.H. Hotel
Mary Doe Teacher 1/12/1975 1/12/1986 Rhodant Middle School
Mary Doe...

I found a way to count the number of years as teacher for each member but I
need to produce something that can count years in each position by member.
Do I need to create a separate query for each job title (600+)?
I need something like this on my report:

Jane Smith (years)
Teacher (4)
Consultant (1)
Sabbatical (2)
Housekeeper (1)
Mary Doe
Teacher (10)

This query will be added as a subreport to an existing report full of member
information that is sorted by celebration location. If I can get the query I
will be good to go!

Any help is very much appreciated.