Grouping Query on a date range 2

[LDG1234]

HI guys... i hope that you can help me out.

I have a mass of data which I need to analyse, specifically by lots of different criteria, but I need to calculate in blocks of months.

Does anyone know how to group on calender months??

Eg.

Date Volume
01/01/01 2
04/01/01 3
08/01/01 5
01/02/01 1
06/02/01 4
19/02/01 6

...to return

Month(or date range) Volume
01/01 10
02/01 11

Any ideas??

Many thanks






Lloyd Gozzett
Process Developer

 

[Craig0201]

Lloyd,

You need to use Format on the date....

Format(YourField,"mm/yy&quot

This can then be grouped and the other field counted/summed/whatevered.....

Hope that helps

Craig

 

[mdthornton]

select format([myTable]![myDate], "mm/yy&quot as mDate, sum([myTable]![myVolume]) as volume
from myTable
group by format([myTable]![myDate], "mm/yy&quot

or if you want to keep as a date/time column:

select dateserial(Year([myTable]![myDate]), Month([myTable]![myDate]), 1) as mDate, sum([myTable]![myVolume]) as volume
from myTable
group by dateserial(Year([myTable]![myDate]), Month([myTable]![myDate]), 1)

converts all dates to 1st of month Best Regards,
Mike

 

[LDG1234]

Thanks guys......thats really helpful Lloyd Gozzett
Process Developer