By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,220 Members | 956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,220 IT Pros & Developers. It's quick & easy.

Summing Data by Calendar Week

P: 1
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.
Aug 6 '07 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,669
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
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(tblCrosstab.Sales) AS Sum_Of_Sales_By_Week
  2. SELECT tblCrosstab.Title, Sum(tblCrosstab.Sales) AS [Tota Sales By Week]
  3. FROM tblCrosstab
  4. GROUP BY tblCrosstab.Title
  5. PIVOT Format([Date],"ww") In ("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18",
  6. "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");
Aug 6 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.