Query dates for Chart

  • Thread starter Thread starter m stroup
  • Start date Start date
M

m stroup

I have tblEmployee with [employeeID], [name], [startdt], [enddt], etc.

I want to build a chart/report which will give me the number of employees
employed by month (Jan07, Feb07, etc.) .

I can create a query with an expression for Jan07 which returns a true
result as expected.

I just think there is a better way to do this. I feel like I am "forcing"
the issue.
Any suggestions?
 
It would help to have a table/query of each year and month in the entire
range required. You could then add this table/query to a query with
tblEmployee (no joins) and set the criteria under the YearFld * 100 +
MonthFld to
Between Year(StartDt) * 100 + Month(StartDt) AND Year(EndDt) * 100 +
Month(EndDt)
This will result in one record per employee per month they are employed. You
can group by YearFld and MonthFld and count the number of employees for your
chart.
 
Back
Top