Return Date and Count ?? 1

[hello101]

Hi everyone,
I have a table similar to this one:

Salesman_ID .............HireDate.......... Qutting Date

123454 ..................1/1/2006
333365 ..................2/3/2006.............4/5/2006
4546463..................5/6/2006
325535 ..................4/8/2006

I need to return each day of 2006 until today and how many salesmen were active at each day. (Active means they have been hired and haven't quit yet)....

In other words the number might not change from one day to the next, but it also might increase and decrease since new salesmen can be hired or fired/quit.

I'm not sure how to use the Date method..Do i need it here..any ideas..Tks in advance.
Richard,

 

[PHV]

Have a table named, say, tblDates with all the dates you'll need (easy to create such table in Excel and then import it).
Now your Query (SQL code):
SELECT D.DateField, Count(S.Salesman_ID) AS CountOfActive
FROM tblDates AS D, tblSales AS S
WHERE D.DateField Between #2006-01-01# And Date()
AND S.HireDate <= D.DateField
AND (S.[Qutting Date] >= D.DateField OR S.[Qutting Date] Is Null)
GROUP BY D.DateField

Variation on the theme:
SELECT D.DateField, Count(S.Salesman_ID) AS CountOfActive
FROM tblDates AS D, tblSales AS S
WHERE D.DateField Between #2006-01-01# And Date()
AND D.DateField Between S.HireDate And Nz(S.[Qutting Date], #2049-12-31#)
GROUP BY D.DateField

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886