Sum Last 4 Quarters

[jas580]

I have a query that returns the first 2 columns below. I want to add the 3rd
column:

Date Revenue Revenue Past 4 Qs
6/30/2007 100 730
3/31/2007 250 1170
12/31/2006 120 1150
9/30/2006 260 n/a (only 3 Qs)
6/30/2006 540 n/a (only 3 Qs)
3/31/2006 230 n/a (only 3 Qs)

The 3rd column should sum the current Q plus the past 3 Qs (Trailing 4Q).
I'm not sure how to accomplish this. Thanks for the help...

-jeff

 

[NetworkTrade]

Off the cuff; I believe you will need to add a sequential numbering column so
that you have a reference other than the date field to identify the previous
three records... ie. current number
current number - 1
current number - 2
current number - 3
then create the right sql statement for which I'll probably make a bunch of
mistakes if I tried....sorry for the half *** answer....hopefully a full time
sql-er will jump in..

 

[jas580]

Do you know how I can create a sequential numbering column in the query?

 

[jas580]

I figured out how to number the records like you said. Not sure what to do
now...

-j

 

[Klatuu]

This expression will give you a date 3 quarters prior to the date you use:
dateadd("q",-3,#6/20/2007#)
Will Return 9/20/2006

 

[NetworkTrade]

First let me say that Klatuu is an Access expert and has helped me many many
times.... I don't recommend relying on the date column because of bad
experiences with humans ie. management/customers : just as soon as you get it
set up for the last day of the quarter, they will change the reporting to the
15th of the month and blame you for the problem.....so I think it better to
rely on an independent sequential numerical column as your reference for
which records to sum.....am calling this column/field 'sequence' ; should be
straight old numbering of each record 1 thru whatever....

that being said...this is what you need to do....could not rip it off the
cuff in earlier reply...had to double check my syntax; which is ok as I
needed to have this figured out myself...this will work:
a. set up a query, and select the table - I will call it 'TableName'
b. add your sequence field, revenue field, date field
c. up in the menu go to 'view' and select 'SQL view'
d. it will give you the sql statement for what you have so far...alter it to
read exactly like below(copy paste this into the sql view).....obviously
replace my generic names for your actual names in all cases.....T1 is an
abstract duplicate table that is a literal...leave that as-is.
then press the ! it does work...
-----------------------------------------------------------
SELECT Sequence, Revenue,Date,

(SELECT Sum(TableName.Revenue) AS RevenueOf4QTRs

FROM TableName

WHERE TableName.Sequence <= T1.Sequence AND
TableName.Sequence>=(T1.Sequence-3)) AS RevenueOf4QTRs

FROM TableName AS T1;

 

[jas580]

Perfect, thank you NTC!

First let me say that Klatuu is an Access expert and has helped me many many
times.... I don't recommend relying on the date column because of bad
experiences with humans ie. management/customers : just as soon as you get it
set up for the last day of the quarter, they will change the reporting to the
15th of the month and blame you for the problem.....so I think it better to
rely on an independent sequential numerical column as your reference for
which records to sum.....am calling this column/field 'sequence' ; should be
straight old numbering of each record 1 thru whatever....

that being said...this is what you need to do....could not rip it off the
cuff in earlier reply...had to double check my syntax; which is ok as I
needed to have this figured out myself...this will work:
a. set up a query, and select the table - I will call it 'TableName'
b. add your sequence field, revenue field, date field
c. up in the menu go to 'view' and select 'SQL view'
d. it will give you the sql statement for what you have so far...alter it to
read exactly like below(copy paste this into the sql view).....obviously
replace my generic names for your actual names in all cases.....T1 is an
abstract duplicate table that is a literal...leave that as-is.
then press the ! it does work...
-----------------------------------------------------------
SELECT Sequence, Revenue,Date,

(SELECT Sum(TableName.Revenue) AS RevenueOf4QTRs

FROM TableName

WHERE TableName.Sequence <= T1.Sequence AND
TableName.Sequence>=(T1.Sequence-3)) AS RevenueOf4QTRs

FROM TableName AS T1;