Hrm... with this query you are not choosing a static set of weeks. For instance if you ran this today, then ran it again tomorrow you'd get different results even if none of the data had changed because some data would have moved weeks, is that what you intended? Or are you taking greater pains to suppress 'wrong' date selections in your actual database?
You have two possibilities with this query
1. The year ends ends on a saturday which coincides exactly with your week function so you have no problems (ends at 52 weeks)
2. the year doesn't end on a saturday and you have a problem (ends at 53 weeks)
What your query is actually doing is throwing december 29, 30 and 31 into week 53 of the 'old year' and january 1, 2, 3 and 4 into week 1 of the new year.
so try this:
Code:
TRANSFORM Count(Loads.PRO) AS CountOfPRO
SELECT Loads.CustomerID
FROM Loads
WHERE (((Loads.PickupDate) Between Date()-112 And Date()))
GROUP BY Loads.CustomerID
ORDER BY IIf(DatePart("ww",[pickupdate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"yyyy-ww")),Format([pickupdate],"yyyy-ww")) DESC
PIVOT IIf(DatePart("ww",[pickupdate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"yyyy-ww")),Format([pickupdate],"yyyy-ww"));
I'm basically saying that if the week is 53, convert the date of the first day of the year's week rather than using the 53 value.