M
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.
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.