grouping up date data

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

I have a dataset that has PO info, with item#, item volume, etc and
associated date of PO for each recordset.

If I want to group up the data into weeks or months or years and get
summed up item volumes per week, how would I do this?

i.e.


po item volume date
123 5416 123,456 3/1/2009
124 5417 90,874 3/2/2009
125 5416 145,542 3/2/2009
126 5418 456,485 3/3/2009

how can I get the week summed up for each item below?
so it' would look like

item volume week year
5416 268,998 9 2009
5417 90,874 9 2009
5418 456,485 9 2009

etc for months and years. Is this possible?

Ryan
 
You can use the DatePart function with the "ww" argument to
get the week number, and the Month and Year functions to get
the month or year from you date field. The SQL for the example
query in your post might look like;

SELECT Item, Sum([Volume]) AS SumOfVolume,
DatePart("ww", [DateField]) AS Week,
Year([DateField]) AS Year FROM tblYourTable
GROUP BY Item, DatePart("ww", [DateField]) , Year([DateField])
 
Back
Top