sum weekly data into monthly
in [Queries]
|
Prev: Long list of fields, how to check if either is null
Next: Union query not displaying select statement
From: drunkle on 27 Apr 2010 11:54 I want to sum weekly data into monthly data by SKU. I have two tables. the first looks like this: sku 20100426 20100503 20100510 20100517 20100524 abc 200 300 200 150 150 where the column heading of 20100426 = week ending 4/26/2010 the second table looks like this: DATE Fiscal Month 20100426 6 20100503 6 20100510 6 20100517 7 20100524 7 I want to sum SKU abc by fiscal mont. For month 6 it would equal 700 month 7 would equal 300. hate to admit it but this one is beyond me.
From: Jeff Boyce on 27 Apr 2010 12:09
?!? You have a column named for the "week ending date?" Doesn't this require you to always be adding new columns? That may be the approach you'd use if you were limited to using a spreadsheet, but Access is a relational database. The way your data is structured pretty much guarantees you & Access will have to struggle with work-arounds. Is there a reason you aren't just using a spreadsheet? Take a look at the table design below ... it may offer you (and Access) a way to do much less work: tblWeeklySKU WeeklySKU_ID WeekEndingDate SKU Amt (I'm not clear what it is that you are measuring...?inventory?) If you want to summarize by month, use a query and the Month() function to determine SKUs and Amts in any given month. Two potential concerns: 1) How do you propose to handle a week-ending period that crosses over from one month to another? Which month is the SKU amount "in"? 2) It looks like you are only considering a single year (or fiscal year). Are you creating an entirely new database for each year?!? Again, that's a very spreadsheetly approach, and totally unnecessary if you design your data for the strengths Access offers. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "drunkle" <drunkle(a)discussions.microsoft.com> wrote in message news:6CABCC44-FFC1-4093-BC96-FB45AABE825C(a)microsoft.com... >I want to sum weekly data into monthly data by SKU. I have two tables. the > first looks like this: > > sku 20100426 20100503 20100510 20100517 20100524 > abc 200 300 200 150 150 > > where the column heading of 20100426 = week ending 4/26/2010 > > the second table looks like this: > > DATE Fiscal Month > 20100426 6 > 20100503 6 > 20100510 6 > 20100517 7 > 20100524 7 > > I want to sum SKU abc by fiscal mont. For month 6 it would equal 700 > month > 7 would equal 300. > > hate to admit it but this one is beyond me. > |