Calculating Period and Cumulative values...

[John]

I have a table with several months of values:

Month ToDateValue
12/31/07 1000
01/31/08 1500
02/29/08 2300
03/31/08 4000

etc...

What I would like to do is calculate the Period values in a query.

Month ToDateValue PeriodValue
12/31/07 1000 1000
01/31/08 1500 500
02/29/08 2300 800
03/31/08 4000 1700

Is this possible?

tblMonthly is the table name, FnDte is the date field, actualttd is the
todate values.

Thanks in advance!

 

[Allen Browne]

Use a subquery.

Something like this:

SELECT tblMonthly.[Month],
tblMonthly.ToDateValue,
ToDateValue - Nz((SELECT TOP 1 ToDateValue
FROM tblMonthly AS Dupe
WHERE Dupe.[Month] < tblMonthly.[Month]
ORDER BY Dupe.[Month] DESC),0) AS PeriodValue
FROM tblMonthly
ORDER BY tblMonthly.[Month]

For an explanation, see:
http://allenbrowne.com/subquery-01.html#AnotherRecord

(Note: This assumes that Month is uniquely indexed, so there can't be 2
records with the same date.)

 

[John]

Eureka! - Thanks Allen...
--
Thanks - John

Use a subquery.

Something like this:

SELECT tblMonthly.[Month],
tblMonthly.ToDateValue,
ToDateValue - Nz((SELECT TOP 1 ToDateValue
FROM tblMonthly AS Dupe
WHERE Dupe.[Month] < tblMonthly.[Month]
ORDER BY Dupe.[Month] DESC),0) AS PeriodValue
FROM tblMonthly
ORDER BY tblMonthly.[Month]

For an explanation, see:
http://allenbrowne.com/subquery-01.html#AnotherRecord

(Note: This assumes that Month is uniquely indexed, so there can't be 2
records with the same date.)

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

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

I have a table with several months of values:

Month ToDateValue
12/31/07 1000
01/31/08 1500
02/29/08 2300
03/31/08 4000

etc...

What I would like to do is calculate the Period values in a query.

Month ToDateValue PeriodValue
12/31/07 1000 1000
01/31/08 1500 500
02/29/08 2300 800
03/31/08 4000 1700

Is this possible?

tblMonthly is the table name, FnDte is the date field, actualttd is the
todate values.

Thanks in advance!

 

[Marshall Barton]

I have a table with several months of values:

Month ToDateValue
12/31/07 1000
01/31/08 1500
02/29/08 2300
03/31/08 4000

etc...

What I would like to do is calculate the Period values in a query.

Month ToDateValue PeriodValue
12/31/07 1000 1000
01/31/08 1500 500
02/29/08 2300 800
03/31/08 4000 1700

Is this possible?

tblMonthly is the table name, FnDte is the date field, actualttd is the
todate values.

You need a subquery to find the previous record's value so
you can subtract it from the current record's value.

SELECT month, ToDateValue, ToDateValue - (
SELECT TOP 1 ToDateValue
FROM table As X
WHERE X.Month < table.Month
ORDER BY Month DESC
) As PeriodValue
FROM table

 

[Dominic]

Hi there,

I have a situation almost exactly the same, however my date field is not
unique, because there is more than one item audited on that date and they are
put in the same table.
Is there a way i can extract the monthly values for each?
(The goal is then to produce a graph of the monthly values against date,
which a parameter prompt to choose which item I wish to view the history).

Thanks
Dominic

 

[Allen Browne]

If there is no way to distinguish between the entries, then the concept of a
'previous' value is undefined. The structure is inadequate for what you want
to do with it.