rolling time periods - any help appreciated...

[ac512]

Hello

I am using Access 2000, and have a query which uses totals to group by
fields named 'AREA' and 'MONTH', and the query provides a sum of a field
named 'UNITS'. Is there any way that I can easily group the 'MONTH' field
into 3 month intervals (which may not be standard quarterly periods)?

Hope this makes sense? Any assistance/guidance would be greatly appreciated.

Thanks in advance
Kind regards
AC

 

[Krzysztof Pozorek [MVP]]

(...)

named 'UNITS'. Is there any way that I can easily group the 'MONTH' field
into 3 month intervals (which may not be standard quarterly periods)?

Use Choose function:
interval=Choose([Month],1,1,2,1,2,3,2,3,3,1,2,3)

In this example:
Month1,2,4,10 -> interval 1
Month 3,4,5,11 -> interval 2
Month 6,8,9,12 -> interval 3

K.P., MVP

 

[ac512]

Hi Krzysztof

Thank you very much for your response - much appreciated.
Is there any way that this can work for a rolling 3 month period?

Thanks again

Kind regards
AC

(...)

named 'UNITS'. Is there any way that I can easily group the 'MONTH' field
into 3 month intervals (which may not be standard quarterly periods)?

Use Choose function:
interval=Choose([Month],1,1,2,1,2,3,2,3,3,1,2,3)

In this example:
Month1,2,4,10 -> interval 1
Month 3,4,5,11 -> interval 2
Month 6,8,9,12 -> interval 3

K.P., MVP

 

[John Spencer]

What is stored in the MONTH field? Is it a Date field? A number field? Or
is it a function that you have used to extract the month from a date field?

Can you post the SQL statement of your current query?

Can you describe more fully how you want to group the records into quarters?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Hello

I am using Access 2000, and have a query which uses totals to group by
fields named 'AREA' and 'MONTH', and the query provides a sum of a field
named 'UNITS'. Is there any way that I can easily group the 'MONTH' field
into 3 month intervals (which may not be standard quarterly periods)?

Hope this makes sense? Any assistance/guidance would be greatly appreciated.

Thanks in advance
Kind regards
AC

An alternative to Krzysztof's (very flexible!) Choose function, assuming that
your MONTH field is an integer month number, would use some arithmatic:

Period: (Month(Date()) - [MONTH]) \ 3

This will (currently) be 0 for September, August and July, 1 for June, May,
and April, etc. This will of course fail for date ranges spanning the end of
the year - how do you distinguish December 2008 from December 2007??

 

[ac512]

Thank you all for your responses.

The month field is a date field in format mmm-yy, and the sql is as follows:

SELECT tblRMB520.ProductGroup, tblRMB520.[State Name], tblRMB520.Month,
Sum(tblRMB520.WU) AS Units
FROM tblRMB520
GROUP BY tblRMB520.ProductGroup, tblRMB520.[State Name], tblRMB520.Month;

I would like to group by moving 3 month periods so that I will have data
grouped for the last 3 months, and then the previous 3 months etc. EG. with
the current month of September 08, I would like to group the months using
June, July, August 08 as 1 group, March, April, May 08 as the previous group
and so on. Next month (October 08), I would group the 3 months as July,
August, September 08 as 1 group, April, May, June 08 as another group and so
on.

Thank you for your continued assistance - much appreciated

Kind regards
AC

 

[John Spencer]

Assuming that Month is really a date field and not a text field
containing things like Jan 08.

And that you have a start date parameter.
And that you want one year of data

I think the following untested query may come close to doing what you want.

Parameters [Start Date] DateTime;
SELECT ProductGroup
, [State Name]
, 4-DateDiff("q",Month,[Start Date]) as Quarter
, Sum(WU) as Units
FROM tblRMB520
WHERE Month Between DateAdd("yyyy",-1,[Start Date]) AND [Start Date]
GROUP BY ProductGroup
, [State Name]
, 4-DateDiff("q",Month,[Start Date])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thank you all for your responses.

The month field is a date field in format mmm-yy, and the sql is as follows:

SELECT tblRMB520.ProductGroup, tblRMB520.[State Name], tblRMB520.Month,
Sum(tblRMB520.WU) AS Units
FROM tblRMB520
GROUP BY tblRMB520.ProductGroup, tblRMB520.[State Name], tblRMB520.Month;

I would like to group by moving 3 month periods so that I will have data
grouped for the last 3 months, and then the previous 3 months etc. EG. with
the current month of September 08, I would like to group the months using
June, July, August 08 as 1 group, March, April, May 08 as the previous group
and so on. Next month (October 08), I would group the 3 months as July,
August, September 08 as 1 group, April, May, June 08 as another group and so
on.

Thank you for your continued assistance - much appreciated

Kind regards
AC

What is stored in the MONTH field? Is it a Date field? A number field? Or
is it a function that you have used to extract the month from a date field?

Can you post the SQL statement of your current query?

Can you describe more fully how you want to group the records into quarters?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County