Using BETWEEN in variable entries

[jtmurphy]

Hello all,

I have a number of call agents with different hire dates, I would like to know how many calls each of them accepted in their first month, second month, etc. I can get their total number of calls accepted but get stuck at that point. Here's my query at this time:

SELECT PhoneData.[CL Username], AgentRoster.[Hire Date], AgentRoster.[Hire Date]+30, SUM(PhoneData.Accepted)
FROM PhoneData, AgentRoster
WHERE AgentRoster.[CL Username]=PhoneData.[CL Username]


GROUP BY PhoneData.[CL Username], AgentRoster.[Hire Date], AgentRoster.[Hire Date]+30

Is BETWEEN the right way to go, or is there an option I'm not thinking of. Any help would be greatly appreciated, thanks!

John

[June7]

So you need to group by agent and month? Maybe:

SELECT PhoneData.[CL Username], Format([CallDate], "yyyymm") AS YrMo, SUM(PhoneData.Accepted)
FROM PhoneData, AgentRoster
INNER JOIN AgentRoster.[CL Username]=PhoneData.[CL Username]
GROUP BY PhoneData.[CL Username], Format([CallDate], "yyyymm");

[jtmurphy]

So you need to group by agent and month? Maybe:

SELECT PhoneData.[CL Username], Format([CallDate], "yyyymm") AS YrMo, SUM(PhoneData.Accepted)
FROM PhoneData, AgentRoster
INNER JOIN AgentRoster.[CL Username]=PhoneData.[CL Username]
GROUP BY PhoneData.[CL Username], Format([CallDate], "yyyymm");[/QUOTE]

The trouble I'm having is how to grab only the first month's data with varying hire dates. For example,

Agent #1 - 5/1/14 (just want the data from 5/1 - 6/1)
Agent #2 - 6/15/14 (just want the data from 6/15 - 7/15)
Agent #3 - 11/10/13 (just want the data from 11/10 - 12/10)

Is there a way to distinguish that in a query? Or will I just have to set up a query for each class?

[June7]

Maybe:

WHERE Format([CallDate], "yyyymm") = Format([HireDate], "yyyymm")