SQL and Criteria help - sums, group and categories - Access 2010

[craigmase]

Please save the wall from my banging head!!

I have 3 fields i'm trying to produce information from
Recovery handler
Recovered amount
Date logged
which is from the Table - TBL_CASES

I'm trying to produce information of total sum recovered, per month, per handler

the SQL i have at moment is
"SELECT ([Recovery Handler]) as Handler, Sum(TBL_CASES.[actual recovery]) AS Recovered
FROM TBL_CASES
GROUP BY ([Recovery Handler])"

this so far has a total recovered per handler, but not by month

When I've tried grouping on crosstab it only picks up recoveries logged on the 1st of each month

 

[Joe4]

Try something like:

[COLOR=#333333]SELECT ([Recovery Handler]) as Handler, Format([Date logged],"yymm") as MonthLogged, Sum([actual recovery]) AS Recovered[/COLOR]
[COLOR=#333333]FROM TBL_CASES[/COLOR]
[COLOR=#333333]GROUP BY [Recovery Handler], [/COLOR][COLOR=#333333]Format([Date logged],"yymm")[/COLOR]

 

[craigmase]

Thanks Joe