Trend by year report

[McLiguori]

I have a db that includes (among others) the following fields: Separation Year; Hire Year; Promotion Year.

I need to create a query from which I can then create a report that will give me the separations, hires and promotions by year.

I also need the report to generate the total number of employees for the various years, so that, for example, if the total number of employees for 1980 was 5000 (which is a fixed starting figure)the query or report would generate a line for 1981 which would give the total number of separations, hires and promotions for 1981. It would also take the total employee number for 1980 (i.e. 5000) add the number of 1981 hires and subtract the number of 1981 separations.

The end result I need would be something like:
Year Total Hires Separations Promotions
1980 5000 50 10 5
1981 5020 30 10 5
(in this example the query or report generates that there were 30 hires, 10 separations and 5 promotions in 1981. It then takes the total from 1980 adds the 1981 hires and subtracts the 1981 separations giving a 1981 total of 5020.

The row for 1982 would generate the number of hires, separations and promotions, then take the total from 1981 and subtact the separations for 1982 and add the hires for 1982 thus giving the total for 1982.

Can anyone please help me with this problem?

Thanks!

McLigs

 

[dhookom]

I would first create a table of all years [tblYears] with a single field [TheYear] and values 1980 to present (or whatever).
You can then calculate the number of employees by creating a query with your un-named table and tblYears.

Try something like this for your sql view:

SELECT [TheYear], Count(TheYear) as TotalEmployees,
Sum(Abs(TheYear = [Hire Year])) As Hires,
Sum(Abs(TheYear = [Separation Year])) As Separations,
Sum(Abs(TheYear = [Promotion Year])) As Promotions
From tblYear, tblNoNameGiven
WHERE TheYear Between [Hire Year] And [Separation Year]
GROUP BY TheYear;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[McLiguori]

Thanks Dhookom,

I have most of it working using your suggestions. I had simplified my query structure, but have adapted your suggestions to it.

Thanks Much!

McL

 

[SkipVought]

Hi,

Also could be done with a UNION query like...

Separation Year; Hire Year; Promotion Year
select 
  [Hire Year] As Year
,Count(*) as 'Hires'
, 0 as 'Separations'
, 0 as 'Promotions'
from [YourTable]
UNION
select 
  [Separation Year] 
, 0
, Count(*)
, 0
from [YourTable]
UNION
select 
  [Promotion Year] 
, 0
, 0
, Count(*)
from [YourTable]

Skip,

_[red][/red]

 

[McLiguori]

Thanks Skip. I'll try it out and let you know.

McLigs