sum weekly data into monthly

  • Thread starter Thread starter drunkle
  • Start date Start date
D

drunkle

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.
 
?!? 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.
 
Back
Top