Format Crosstab query to show totals in Week Ending groups

[KimberlyC]

Hi!
I'm using the below SQL statement to create a crosstab query.
It works great ... except I need the Pivot "part" to group the compelted
dates by Week ending .
It's showing the completed dates group by by weekday.
How do I do this????


TRANSFORM Count(completed.[Assignment Number]) AS [CountOfAssignment Number]
SELECT completed.SalesRep, Count(completed.[Assignment Number]) AS [Total Of
Assignment Number]
FROM completed
GROUP BY completed.SalesRep
PIVOT Format$([Completed date],"mmmm dd yyyy",0,0);


Thanks in advance for your help!
Kimberly

 

[John Spencer (MVP)]

Try using the format function to force all dates to return a year and week
number. I think that might be -
PIVOT Format([Completed Date],"yyyy-ww",0,0)

or force all the dates to be the same for each week using some date arithmetic.
For instance
DateAdd("d",2-Weekday([Completed Date]),[Completed Date])
should adjust all the dates to be Monday of the week.

PIVOT DateAdd("d",2-Weekday([Completed Date]),[Completed Date])