Query dates for Chart

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?
 
D

Duane Hookom

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