Grouping periods & running sum - PLEASE help if you can!

[ac512]

Hello

I have endeavoured to get assistance with this problem in the past, but I
think part of the problem was my explanation of what I want to do. As a
result, I will attempt to provide a detailed explanation of what I require
(apologies - please bare with me!).
I am using Access 2007, and have a query that calculates a count of units as
per this example:
MONTH UNITCOUNT
Oct-07 5
Nov-07 7
Dec-07 3
Jan-08 9
Feb-08 6
Mar-08 8
Apr-08 9
May-08 6
Jun-08 3
Jul-08 5
Aug-08 3
Sep-08 7

The MONTH column is a a date field formatted as mmm-yy, while the unit field
is an integer. I am trying to use this query to calculate a running 3 month
count. EG. using the above table, the results will look like the following:

MONTH UNIT COUNT
Dec-07 15
Jan-08 19
Feb-08 18
Mar-08 23
Apr-08 23
May-08 23
Jun-08 18
Jul-08 14
Aug-08 11
Sep-08 15

So, I am trying to group the date field into 3 month intervals (which I
don't think is neccessarily solved by using any references to quarters as a
time period?), and count the units so that they show the aggregated results
of the current month and the previous 3 months (as per the table above). One
thing to note, is that my date field spans over more than one year. Next
month, Oct-08 results will aggregate the October units with the September &
August units.

I really hope this all makes sense?

Any assistance with this problem will be greatly appreciated. I have been
stuck on this one for some time.

Thank you very much in advance

Kind regards
AC

 

[Allen Browne]

Use a subquery.

This example assumes the month coloumn is named Month (since Month is a
function name, it can confuse Access), and the actual date is always the
first of the month (with no time component):

SELECT TheMonth,
(SELECT Sum(UnitCount) AS TheCount
FROM Table1 AS Dupe
WHERE Dupe.TheMonth Between DateAdd("m", -2, Table1.TheMonth)
AND Table1.TheMonth) AS ThreeMonthCount
FROM Table1;

If subqueries are new, see
http://allenbrowne.com/subquery-01.html

 

[ac512]

Hi Allen

I very much appreciate your help - your assistance has always proven
invaluable.

I referenced your very informative website on using subqueries, and tried to
use one to compare data in a table from this year to the same period last
year (monthly). Unfortunately, there must be a problem with my syntax as it
doesn't achieve the desired results.
If you would have a moment to review my sql, I would be most appreciative
(don't hesitate to advise if I am not following the correct
process/protocol!).

My table does not have a unique identifier, as it is based on an external
table, and therefore have create several links.

My sql is as follows:

SELECT tblTest.AcctMonth, Sum(tblTest.WU) AS WrittenUnits,
(SELECT Sum(Dupe.WU) AS WrittenUnitsLastYear
FROM tblTest INNER JOIN tblTest AS Dupe ON (tblTest.SubClass =
Dupe.SubClass) AND (tblTest.Class = Dupe.Class) AND (tblTest.[State Name] =
Dupe.[State Name]) AND (tblTest.Company = Dupe.Company) AND
(tblTest.ProductGroup = Dupe.ProductGroup)
where Dupe.AcctMonth =
dateserial(year(tblTest.AcctMonth)-1,month(tblTest.AcctMonth),1)
) AS LastYearValues
FROM tblTest
GROUP BY tblTest.AcctMonth;


As always, I am extremely thankful of any guidance/advice you could provide.

Thanks in advance
Kind regards
AC

 

[Allen Browne]

Can you be more specific about the results you get?
About double? Too low? Shows zeros? Yields no records?

What kind of data file is this? For example, if it is text, Access may not
understand AcctMonth to be a date/time type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I very much appreciate your help - your assistance has always proven
invaluable.

I referenced your very informative website on using subqueries, and tried
to
use one to compare data in a table from this year to the same period last
year (monthly). Unfortunately, there must be a problem with my syntax as
it
doesn't achieve the desired results.
If you would have a moment to review my sql, I would be most appreciative
(don't hesitate to advise if I am not following the correct
process/protocol!).

My table does not have a unique identifier, as it is based on an external
table, and therefore have create several links.

My sql is as follows:

SELECT tblTest.AcctMonth,
Sum(tblTest.WU) AS WrittenUnits,
(SELECT Sum(Dupe.WU) AS WrittenUnitsLastYear
FROM tblTest INNER JOIN tblTest AS Dupe
ON (tblTest.SubClass = Dupe.SubClass)
AND (tblTest.Class = Dupe.Class)
AND (tblTest.[State Name] = Dupe.[State Name])
AND (tblTest.Company = Dupe.Company)
AND (tblTest.ProductGroup = Dupe.ProductGroup)
WHERE Dupe.AcctMonth = dateserial(year(tblTest.AcctMonth)-1,
month(tblTest.AcctMonth), 1)) AS LastYearValues
FROM tblTest
GROUP BY tblTest.AcctMonth;

 

[ac512]

Hi Allen

Thanks for your response.
The date field (AcctMonth) is formatted as a date, and displays as the first
of every month (eg. Apr-08 = 01/04/2008). The table has monthly data from
Jan-06 to Sep-08, and as I have not put any date constraints on my query, I
am getting the correct monthly 'WrittenUnits' value under 'WrittenUnits',
however, under 'LastYearValues' I am getting a constant figure - which just
so happens to be the sum of the 'WrittenUnits' from Jan-06 to Sep-07.
Hope this makes sense, and I really appreciate your help

Kind regards
AC

Can you be more specific about the results you get?
About double? Too low? Shows zeros? Yields no records?

What kind of data file is this? For example, if it is text, Access may not
understand AcctMonth to be a date/time type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I very much appreciate your help - your assistance has always proven
invaluable.

I referenced your very informative website on using subqueries, and tried
to
use one to compare data in a table from this year to the same period last
year (monthly). Unfortunately, there must be a problem with my syntax as
it
doesn't achieve the desired results.
If you would have a moment to review my sql, I would be most appreciative
(don't hesitate to advise if I am not following the correct
process/protocol!).

My table does not have a unique identifier, as it is based on an external
table, and therefore have create several links.

My sql is as follows:

SELECT tblTest.AcctMonth,
Sum(tblTest.WU) AS WrittenUnits,
(SELECT Sum(Dupe.WU) AS WrittenUnitsLastYear
FROM tblTest INNER JOIN tblTest AS Dupe
ON (tblTest.SubClass = Dupe.SubClass)
AND (tblTest.Class = Dupe.Class)
AND (tblTest.[State Name] = Dupe.[State Name])
AND (tblTest.Company = Dupe.Company)
AND (tblTest.ProductGroup = Dupe.ProductGroup)
WHERE Dupe.AcctMonth = dateserial(year(tblTest.AcctMonth)-1,
month(tblTest.AcctMonth), 1)) AS LastYearValues
FROM tblTest
GROUP BY tblTest.AcctMonth;

As always, I am extremely thankful of any guidance/advice you could
provide.

Thanks in advance
Kind regards
AC

 

[Allen Browne]

Okay, we still don't know what kind of data source this is, and therefore
what type of field this is (or a least, how JET is interpreting the data.)

Given the outcome, my guess is that it is performing some kind of string
comparison rather than date comparison.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response.
The date field (AcctMonth) is formatted as a date, and displays as the
first
of every month (eg. Apr-08 = 01/04/2008). The table has monthly data from
Jan-06 to Sep-08, and as I have not put any date constraints on my query,
I
am getting the correct monthly 'WrittenUnits' value under 'WrittenUnits',
however, under 'LastYearValues' I am getting a constant figure - which
just
so happens to be the sum of the 'WrittenUnits' from Jan-06 to Sep-07.
Hope this makes sense, and I really appreciate your help

Kind regards
AC

Can you be more specific about the results you get?
About double? Too low? Shows zeros? Yields no records?

What kind of data file is this? For example, if it is text, Access may
not
understand AcctMonth to be a date/time type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I very much appreciate your help - your assistance has always proven
invaluable.

I referenced your very informative website on using subqueries, and
tried
to
use one to compare data in a table from this year to the same period
last
year (monthly). Unfortunately, there must be a problem with my syntax
as
it
doesn't achieve the desired results.
If you would have a moment to review my sql, I would be most
appreciative
(don't hesitate to advise if I am not following the correct
process/protocol!).

My table does not have a unique identifier, as it is based on an
external
table, and therefore have create several links.

My sql is as follows:

SELECT tblTest.AcctMonth,
Sum(tblTest.WU) AS WrittenUnits,
(SELECT Sum(Dupe.WU) AS WrittenUnitsLastYear
FROM tblTest INNER JOIN tblTest AS Dupe
ON (tblTest.SubClass = Dupe.SubClass)
AND (tblTest.Class = Dupe.Class)
AND (tblTest.[State Name] = Dupe.[State Name])
AND (tblTest.Company = Dupe.Company)
AND (tblTest.ProductGroup = Dupe.ProductGroup)
WHERE Dupe.AcctMonth = dateserial(year(tblTest.AcctMonth)-1,
month(tblTest.AcctMonth), 1)) AS LastYearValues
FROM tblTest
GROUP BY tblTest.AcctMonth;

As always, I am extremely thankful of any guidance/advice you could
provide.

Thanks in advance
Kind regards
AC

:

Use a subquery.

This example assumes the month coloumn is named Month (since Month is
a
function name, it can confuse Access), and the actual date is always
the
first of the month (with no time component):

SELECT TheMonth,
(SELECT Sum(UnitCount) AS TheCount
FROM Table1 AS Dupe
WHERE Dupe.TheMonth Between DateAdd("m", -2, Table1.TheMonth)
AND Table1.TheMonth) AS ThreeMonthCount
FROM Table1;

If subqueries are new, see
http://allenbrowne.com/subquery-01.html

Hello

I have endeavoured to get assistance with this problem in the past,
but
I
think part of the problem was my explanation of what I want to do.
As
a
result, I will attempt to provide a detailed explanation of what I
require
(apologies - please bare with me!).
I am using Access 2007, and have a query that calculates a count of
units
as
per this example:
MONTH UNITCOUNT
Oct-07 5
Nov-07 7
Dec-07 3
Jan-08 9
Feb-08 6
Mar-08 8
Apr-08 9
May-08 6
Jun-08 3
Jul-08 5
Aug-08 3
Sep-08 7

The MONTH column is a a date field formatted as mmm-yy, while the
unit
field
is an integer. I am trying to use this query to calculate a running
3
month
count. EG. using the above table, the results will look like the
following:

MONTH UNIT COUNT
Dec-07 15
Jan-08 19
Feb-08 18
Mar-08 23
Apr-08 23
May-08 23
Jun-08 18
Jul-08 14
Aug-08 11
Sep-08 15

So, I am trying to group the date field into 3 month intervals
(which I
don't think is neccessarily solved by using any references to
quarters
as
a
time period?), and count the units so that they show the aggregated
results
of the current month and the previous 3 months (as per the table
above).
One
thing to note, is that my date field spans over more than one year.
Next
month, Oct-08 results will aggregate the October units with the
September
&
August units.

I really hope this all makes sense?

Any assistance with this problem will be greatly appreciated. I
have
been
stuck on this one for some time.

 

[ac512]

Hello again Allen

I actually import this data into my access database, and the 'AcctMonth'
field is a date/time field, and the WrittenUnits field is a number (double).
Apologies if I am not being specific enough

Kind regards
AC

Okay, we still don't know what kind of data source this is, and therefore
what type of field this is (or a least, how JET is interpreting the data.)

Given the outcome, my guess is that it is performing some kind of string
comparison rather than date comparison.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response.
The date field (AcctMonth) is formatted as a date, and displays as the
first
of every month (eg. Apr-08 = 01/04/2008). The table has monthly data from
Jan-06 to Sep-08, and as I have not put any date constraints on my query,
I
am getting the correct monthly 'WrittenUnits' value under 'WrittenUnits',
however, under 'LastYearValues' I am getting a constant figure - which
just
so happens to be the sum of the 'WrittenUnits' from Jan-06 to Sep-07.
Hope this makes sense, and I really appreciate your help

Kind regards
AC

Can you be more specific about the results you get?
About double? Too low? Shows zeros? Yields no records?

What kind of data file is this? For example, if it is text, Access may
not
understand AcctMonth to be a date/time type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I very much appreciate your help - your assistance has always proven
invaluable.

I referenced your very informative website on using subqueries, and
tried
to
use one to compare data in a table from this year to the same period
last
year (monthly). Unfortunately, there must be a problem with my syntax
as
it
doesn't achieve the desired results.
If you would have a moment to review my sql, I would be most
appreciative
(don't hesitate to advise if I am not following the correct
process/protocol!).

My table does not have a unique identifier, as it is based on an
external
table, and therefore have create several links.

My sql is as follows:

SELECT tblTest.AcctMonth,
Sum(tblTest.WU) AS WrittenUnits,
(SELECT Sum(Dupe.WU) AS WrittenUnitsLastYear
FROM tblTest INNER JOIN tblTest AS Dupe
ON (tblTest.SubClass = Dupe.SubClass)
AND (tblTest.Class = Dupe.Class)
AND (tblTest.[State Name] = Dupe.[State Name])
AND (tblTest.Company = Dupe.Company)
AND (tblTest.ProductGroup = Dupe.ProductGroup)
WHERE Dupe.AcctMonth = dateserial(year(tblTest.AcctMonth)-1,
month(tblTest.AcctMonth), 1)) AS LastYearValues
FROM tblTest
GROUP BY tblTest.AcctMonth;


As always, I am extremely thankful of any guidance/advice you could
provide.

Thanks in advance
Kind regards
AC

:

Use a subquery.

This example assumes the month coloumn is named Month (since Month is
a
function name, it can confuse Access), and the actual date is always
the
first of the month (with no time component):

SELECT TheMonth,
(SELECT Sum(UnitCount) AS TheCount
FROM Table1 AS Dupe
WHERE Dupe.TheMonth Between DateAdd("m", -2, Table1.TheMonth)
AND Table1.TheMonth) AS ThreeMonthCount
FROM Table1;

If subqueries are new, see
http://allenbrowne.com/subquery-01.html

Hello

I have endeavoured to get assistance with this problem in the past,
but
I
think part of the problem was my explanation of what I want to do.
As
a
result, I will attempt to provide a detailed explanation of what I
require
(apologies - please bare with me!).
I am using Access 2007, and have a query that calculates a count of
units
as
per this example:
MONTH UNITCOUNT
Oct-07 5
Nov-07 7
Dec-07 3
Jan-08 9
Feb-08 6
Mar-08 8
Apr-08 9
May-08 6
Jun-08 3
Jul-08 5
Aug-08 3
Sep-08 7

The MONTH column is a a date field formatted as mmm-yy, while the
unit
field
is an integer. I am trying to use this query to calculate a running
3
month
count. EG. using the above table, the results will look like the
following:

MONTH UNIT COUNT
Dec-07 15
Jan-08 19
Feb-08 18
Mar-08 23
Apr-08 23
May-08 23
Jun-08 18
Jul-08 14
Aug-08 11
Sep-08 15

So, I am trying to group the date field into 3 month intervals
(which I
don't think is neccessarily solved by using any references to
quarters
as
a
time period?), and count the units so that they show the aggregated
results
of the current month and the previous 3 months (as per the table
above).
One
thing to note, is that my date field spans over more than one year.
Next
month, Oct-08 results will aggregate the October units with the
September
&
August units.

I really hope this all makes sense?

Any assistance with this problem will be greatly appreciated. I
have
been
stuck on this one for some time.

 

[Allen Browne]

Well, I'm not sure what to suggest.

Perhaps you could change the WHERE clause in the subquery to:
WHERE Dupe.AcctMonth = DateAdd("yyyy", -1, tblTest.AcctMonth)


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I actually import this data into my access database, and the 'AcctMonth'
field is a date/time field, and the WrittenUnits field is a number
(double).
Apologies if I am not being specific enough

Okay, we still don't know what kind of data source this is, and therefore
what type of field this is (or a least, how JET is interpreting the
data.)

Given the outcome, my guess is that it is performing some kind of string
comparison rather than date comparison.

The date field (AcctMonth) is formatted as a date, and displays as the
first
of every month (eg. Apr-08 = 01/04/2008). The table has monthly data
from
Jan-06 to Sep-08, and as I have not put any date constraints on my
query,
I
am getting the correct monthly 'WrittenUnits' value under
'WrittenUnits',
however, under 'LastYearValues' I am getting a constant figure - which
just
so happens to be the sum of the 'WrittenUnits' from Jan-06 to Sep-07.
Hope this makes sense, and I really appreciate your help

Can you be more specific about the results you get?
About double? Too low? Shows zeros? Yields no records?

What kind of data file is this? For example, if it is text, Access may
not
understand AcctMonth to be a date/time type.


I very much appreciate your help - your assistance has always proven
invaluable.

I referenced your very informative website on using subqueries, and
tried
to
use one to compare data in a table from this year to the same period
last
year (monthly). Unfortunately, there must be a problem with my
syntax
as
it
doesn't achieve the desired results.
If you would have a moment to review my sql, I would be most
appreciative
(don't hesitate to advise if I am not following the correct
process/protocol!).

My table does not have a unique identifier, as it is based on an
external
table, and therefore have create several links.

My sql is as follows:

SELECT tblTest.AcctMonth,
Sum(tblTest.WU) AS WrittenUnits,
(SELECT Sum(Dupe.WU) AS WrittenUnitsLastYear
FROM tblTest INNER JOIN tblTest AS Dupe
ON (tblTest.SubClass = Dupe.SubClass)
AND (tblTest.Class = Dupe.Class)
AND (tblTest.[State Name] = Dupe.[State Name])
AND (tblTest.Company = Dupe.Company)
AND (tblTest.ProductGroup = Dupe.ProductGroup)
WHERE Dupe.AcctMonth = dateserial(year(tblTest.AcctMonth)-1,
month(tblTest.AcctMonth), 1)) AS LastYearValues
FROM tblTest
GROUP BY tblTest.AcctMonth;


As always, I am extremely thankful of any guidance/advice you could
provide.

Thanks in advance
Kind regards
AC

:

Use a subquery.

This example assumes the month coloumn is named Month (since Month
is
a
function name, it can confuse Access), and the actual date is
always
the
first of the month (with no time component):

SELECT TheMonth,
(SELECT Sum(UnitCount) AS TheCount
FROM Table1 AS Dupe
WHERE Dupe.TheMonth Between DateAdd("m", -2, Table1.TheMonth)
AND Table1.TheMonth) AS ThreeMonthCount
FROM Table1;

If subqueries are new, see
http://allenbrowne.com/subquery-01.html

I have endeavoured to get assistance with this problem in the
past,
but
I
think part of the problem was my explanation of what I want to
do.
As
a
result, I will attempt to provide a detailed explanation of what
I
require
(apologies - please bare with me!).
I am using Access 2007, and have a query that calculates a count
of
units
as
per this example:
MONTH UNITCOUNT
Oct-07 5
Nov-07 7
Dec-07 3
Jan-08 9
Feb-08 6
Mar-08 8
Apr-08 9
May-08 6
Jun-08 3
Jul-08 5
Aug-08 3
Sep-08 7

The MONTH column is a a date field formatted as mmm-yy, while the
unit
field
is an integer. I am trying to use this query to calculate a
running
3
month
count. EG. using the above table, the results will look like the
following:

MONTH UNIT COUNT
Dec-07 15
Jan-08 19
Feb-08 18
Mar-08 23
Apr-08 23
May-08 23
Jun-08 18
Jul-08 14
Aug-08 11
Sep-08 15

So, I am trying to group the date field into 3 month intervals
(which I
don't think is neccessarily solved by using any references to
quarters
as
a
time period?), and count the units so that they show the
aggregated
results
of the current month and the previous 3 months (as per the table
above).
One
thing to note, is that my date field spans over more than one
year.
Next
month, Oct-08 results will aggregate the October units with the
September
&
August units.

I really hope this all makes sense?

Any assistance with this problem will be greatly appreciated. I
have
been
stuck on this one for some time.

 

[ac512]

Many thanks again Allen for all your assistance.

Kind regards
AC

Well, I'm not sure what to suggest.

Perhaps you could change the WHERE clause in the subquery to:
WHERE Dupe.AcctMonth = DateAdd("yyyy", -1, tblTest.AcctMonth)


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I actually import this data into my access database, and the 'AcctMonth'
field is a date/time field, and the WrittenUnits field is a number
(double).
Apologies if I am not being specific enough

Okay, we still don't know what kind of data source this is, and therefore
what type of field this is (or a least, how JET is interpreting the
data.)

Given the outcome, my guess is that it is performing some kind of string
comparison rather than date comparison.

The date field (AcctMonth) is formatted as a date, and displays as the
first
of every month (eg. Apr-08 = 01/04/2008). The table has monthly data
from
Jan-06 to Sep-08, and as I have not put any date constraints on my
query,
I
am getting the correct monthly 'WrittenUnits' value under
'WrittenUnits',
however, under 'LastYearValues' I am getting a constant figure - which
just
so happens to be the sum of the 'WrittenUnits' from Jan-06 to Sep-07.
Hope this makes sense, and I really appreciate your help

Can you be more specific about the results you get?
About double? Too low? Shows zeros? Yields no records?

What kind of data file is this? For example, if it is text, Access may
not
understand AcctMonth to be a date/time type.


I very much appreciate your help - your assistance has always proven
invaluable.

I referenced your very informative website on using subqueries, and
tried
to
use one to compare data in a table from this year to the same period
last
year (monthly). Unfortunately, there must be a problem with my
syntax
as
it
doesn't achieve the desired results.
If you would have a moment to review my sql, I would be most
appreciative
(don't hesitate to advise if I am not following the correct
process/protocol!).

My table does not have a unique identifier, as it is based on an
external
table, and therefore have create several links.

My sql is as follows:

SELECT tblTest.AcctMonth,
Sum(tblTest.WU) AS WrittenUnits,
(SELECT Sum(Dupe.WU) AS WrittenUnitsLastYear
FROM tblTest INNER JOIN tblTest AS Dupe
ON (tblTest.SubClass = Dupe.SubClass)
AND (tblTest.Class = Dupe.Class)
AND (tblTest.[State Name] = Dupe.[State Name])
AND (tblTest.Company = Dupe.Company)
AND (tblTest.ProductGroup = Dupe.ProductGroup)
WHERE Dupe.AcctMonth = dateserial(year(tblTest.AcctMonth)-1,
month(tblTest.AcctMonth), 1)) AS LastYearValues
FROM tblTest
GROUP BY tblTest.AcctMonth;


As always, I am extremely thankful of any guidance/advice you could
provide.

Thanks in advance
Kind regards
AC

:

Use a subquery.

This example assumes the month coloumn is named Month (since Month
is
a
function name, it can confuse Access), and the actual date is
always
the
first of the month (with no time component):

SELECT TheMonth,
(SELECT Sum(UnitCount) AS TheCount
FROM Table1 AS Dupe
WHERE Dupe.TheMonth Between DateAdd("m", -2, Table1.TheMonth)
AND Table1.TheMonth) AS ThreeMonthCount
FROM Table1;

If subqueries are new, see
http://allenbrowne.com/subquery-01.html

I have endeavoured to get assistance with this problem in the
past,
but
I
think part of the problem was my explanation of what I want to
do.
As
a
result, I will attempt to provide a detailed explanation of what
I
require
(apologies - please bare with me!).
I am using Access 2007, and have a query that calculates a count
of
units
as
per this example:
MONTH UNITCOUNT
Oct-07 5
Nov-07 7
Dec-07 3
Jan-08 9
Feb-08 6
Mar-08 8
Apr-08 9
May-08 6
Jun-08 3
Jul-08 5
Aug-08 3
Sep-08 7

The MONTH column is a a date field formatted as mmm-yy, while the
unit
field
is an integer. I am trying to use this query to calculate a
running
3
month
count. EG. using the above table, the results will look like the
following:

MONTH UNIT COUNT
Dec-07 15
Jan-08 19
Feb-08 18
Mar-08 23
Apr-08 23
May-08 23
Jun-08 18
Jul-08 14
Aug-08 11
Sep-08 15

So, I am trying to group the date field into 3 month intervals
(which I
don't think is neccessarily solved by using any references to
quarters
as
a
time period?), and count the units so that they show the
aggregated
results
of the current month and the previous 3 months (as per the table
above).
One
thing to note, is that my date field spans over more than one
year.
Next
month, Oct-08 results will aggregate the October units with the
September
&
August units.

I really hope this all makes sense?

Any assistance with this problem will be greatly appreciated. I
have
been
stuck on this one for some time.