Summing Data by Calendar Week

[JoeyMaac]

Hello. I need to find a way to sum data by calendar week. For example, I receive sales and shipping data from various days during the week. I need to be able to sum this data to get a single value for each software title for each week. This data spans over several years. Any help would be greatly appreciated. Thank you.

[ADezii]

Hello. I need to find a way to sum data by calendar week. For example, I receive sales and shipping data from various days during the week. I need to be able to sum this data to get a single value for each software title for each week. This data spans over several years. Any help would be greatly appreciated. Thank you.

A Crosstab Query with Fixed Column Headings of "1","2","3",..."52" and proper formatting of the Column Headers ( Format([Date],"ww")) should do the trick, but first, a couple of Assumptions:

1. Table Name: tblCrosstab
2. Field Names:
   1. [Date] - DATE/Time
   2. [Title] - TEXT
   3. [Sales] - CURRENCY

TRANSFORM Sum(tblCrosstab.Sales) AS Sum_Of_Sales_By_Week
SELECT tblCrosstab.Title, Sum(tblCrosstab.Sales) AS [Tota Sales By Week]
FROM tblCrosstab
GROUP BY tblCrosstab.Title
PIVOT Format([Date],"ww") In ("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18",
"19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34" ,"35","36","37","38","39","40","41","42","43","44","45","46","47","48","49", "50","51","52");