Percentage change

[Andreas]

Hi all,

I have a table which contains two columns. The first is a date column,
the second contains numerical values. I need to create a query that
gives me the percentage change from the previous row to the current
row in a third column. Here's an example:

Original table:
Date Value
2009-10-11 100
2009-11-30 105
2009-12-03 115
2009-12-23 110
....

Needed result for query:

Date Value %-Change
2009-10-11 100 0.00%
2009-11-30 105 5.00% (=105/100-1)
2009-12-03 115 9.52% (=115/105-1)
2009-12-23 110 -4,35% (=110/115-1)
....


How can I calculate the percentage change within the query?

Regards,
Andreas

 

[Tom van Stiphout]

On Sat, 23 Jan 2010 06:38:13 -0800 (PST), Andreas

Hi Andreas,
You should rename the fields: both are reserved words.
Also take another look at your %-change formula; I think subtracting 1
is not correct.
For SQL it is a bit complicated to get the value of the previous row.
One thing to realize is that the previous row depends on the sort
order. Tables are unsorted, even though of course they have to be
shown in some order (typically: by primary key) when you open them.

Here is the starting point for your query. With this in hand you can
probably finish the task:
SELECT myTable.myDate,
myTable.myValue,
(select max(myDate) from myTable T where myDate < myTable.myDate) AS
prevDate,
(select myValue from myTable T2 where myDate = (select max(myDate)
from myTable T where myDate < myTable.myDate)) AS prevValue
FROM myTable
ORDER BY myTable.myDate;
(replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

 

[Andreas]

On Sat, 23 Jan 2010 06:38:13 -0800 (PST), Andreas


Hi Andreas,
You should rename the fields: both are reserved words.

I know, I named my fields "datDate" and "Balance". The names in the
example were only examples.

Also take another look at your %-change formula; I think subtracting 1
is not correct.

Subtracting 1 is necessary to calculate a percentage change. I am
positive on this. For instance, a change from 100 to 105 is an
increase by 5%. If you just calculate 105/100, you'll get 1.05. You
need to subtract 1 to come up with 0.05, which is equivalent to 5.00%.
This is the percentage change.

For SQL it is a bit complicated to get the value of the previous row.
One thing to realize is that the previous row depends on the sort
order. Tables are unsorted, even though of course they have to be
shown in some order (typically: by primary key) when you open them.

Good point, but my table is sorted by date. So I think I am fine with
that.

Here is the starting point for your query. With this in hand you can
probably finish the task:
SELECT myTable.myDate,
myTable.myValue,
(select max(myDate) from myTable T where myDate < myTable.myDate) AS
prevDate,
(select myValue from myTable T2 where myDate = (select max(myDate)
from myTable T where myDate < myTable.myDate)) AS prevValue
FROM myTable
ORDER BY myTable.myDate;
(replace myObjectNames with yours)

This is perfectly working. Thanks!
However, as I am an ACCESS beginner, I am just wondering whether you
can explain the SQL code to me with a bit more detail. I would like to
learn and understand it. In particular:
- What is the meaning of "T" and "T2"?
- Can you also please explain in plain words how prevDate and
prevValue are calculated, i.e. what exactly is this SQL code
comparing?
- Is this code processed for every row? If so, is max(datDate)
delivering the latest date up to this particular row?

I adjusted the code to work with my table "Test". It now looks like
this:

SELECT Test.datDate, Test.Balance,
(select max(datDate) from Test T where datDate < Test.datDate) AS
prevDate,
(select Balance from Test T2 where datDate = (select max(datDate)
from Test T where datDate < Test.datDate)) AS prevValue,
[Balance]/[prevValue]-1 AS Change
FROM Test
ORDER BY Test.datDate DESC;

Thanks a lot.
Andreas

 

[Al Campagna]

Andreas,
Use a calculation in a query...
Reminder: Don't use the object name Date or Value.
It's a reserved word.
Try TransDate and InvValue for example...

Given that all entries are date ascending, use a DMax of the TransDate
for "any Transdate less than the current record's TransDate"
In a query I used my own example. I created a calculated field called
X:
in a query against my table...
X: DMax("[CustID]","tblCustomers","[TestDate] < #" & [TestDate] & "#")

TestDate CustID X
1/1/2009 2
2/1/2009 3 2
3/1/2009 4 3
4/1/2009 5 4
5/1/2009 6 5
6/1/2009 7 6
7/1/2009 8 7
This method could be used to yield the previous InvValue for use in
the % calc, on the current record.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Tom van Stiphout]

On Sat, 23 Jan 2010 08:12:19 -0800 (PST), Andreas

Yes, you are right about the formula.

Let's look at this fragment a bit more:
(select max(myDate) from myTable T where myDate < myTable.myDate) AS
prevDate
Why are we doing this in the first place? Because we need to get the
previous row, and the PK value of that row uniquely describe it. The
idea is to get the maximum date less than the one in the current row.
SQL is a set-based language so the "do this for each row" part is
automatic.
(select...) is a subselect; it allows you to pull in another query on
a different table, or the same table as in this case.
"as prevDate" names the column.
Why use "myTable T"? This is so we can compare this instance of
myTable with the primary instance. Written a bit more verbosely:
(select max(T.myDate) from myTable T where T.myDate < myTable.myDate)
AS prevDate
Perhaps this shows a bit more clearly that there are two instances of
myTable in play.

The expression for prevValue is more of the same.

-Tom.
Microsoft Access MVP

On Sat, 23 Jan 2010 06:38:13 -0800 (PST), Andreas


Hi Andreas,
You should rename the fields: both are reserved words.

I know, I named my fields "datDate" and "Balance". The names in the
example were only examples.

Also take another look at your %-change formula; I think subtracting 1
is not correct.

Subtracting 1 is necessary to calculate a percentage change. I am
positive on this. For instance, a change from 100 to 105 is an
increase by 5%. If you just calculate 105/100, you'll get 1.05. You
need to subtract 1 to come up with 0.05, which is equivalent to 5.00%.
This is the percentage change.

For SQL it is a bit complicated to get the value of the previous row.
One thing to realize is that the previous row depends on the sort
order. Tables are unsorted, even though of course they have to be
shown in some order (typically: by primary key) when you open them.

Good point, but my table is sorted by date. So I think I am fine with
that.

Here is the starting point for your query. With this in hand you can
probably finish the task:
SELECT myTable.myDate,
myTable.myValue,
(select max(myDate) from myTable T where myDate < myTable.myDate) AS
prevDate,
(select myValue from myTable T2 where myDate = (select max(myDate)
from myTable T where myDate < myTable.myDate)) AS prevValue
FROM myTable
ORDER BY myTable.myDate;
(replace myObjectNames with yours)

This is perfectly working. Thanks!
However, as I am an ACCESS beginner, I am just wondering whether you
can explain the SQL code to me with a bit more detail. I would like to
learn and understand it. In particular:
- What is the meaning of "T" and "T2"?
- Can you also please explain in plain words how prevDate and
prevValue are calculated, i.e. what exactly is this SQL code
comparing?
- Is this code processed for every row? If so, is max(datDate)
delivering the latest date up to this particular row?

I adjusted the code to work with my table "Test". It now looks like
this:

SELECT Test.datDate, Test.Balance,
(select max(datDate) from Test T where datDate < Test.datDate) AS
prevDate,
(select Balance from Test T2 where datDate = (select max(datDate)
from Test T where datDate < Test.datDate)) AS prevValue,
[Balance]/[prevValue]-1 AS Change
FROM Test
ORDER BY Test.datDate DESC;

Thanks a lot.
Andreas

 

[Andreas]

Andreas,
    Use a calculation in a query...
    Reminder: Don't use the object name Date or Value.
    It's a reserved word.
    Try TransDate and InvValue for example...

    Given that all entries are date ascending, use a DMax of the TransDate
for "any Transdate less than the current record's TransDate"
    In a query I used my own example.  I created a calculated fieldcalled
X:
in a query against my table...
X: DMax("[CustID]","tblCustomers","[TestDate] < #" & [TestDate] & "#")

TestDate CustID X
1/1/2009     2
2/1/2009     3     2
3/1/2009     4     3
4/1/2009     5     4
5/1/2009     6     5
6/1/2009     7     6
7/1/2009     8     7
    This method could be used to yield the previous InvValue for use in
the % calc, on the current record.

Hi Al,

I am not completely sure, but I think that you'll get a problem as
soon as your CustID is decreasing. If this is the case, you'll carry
on the highest value from the past. Can you please verify?

Andreas

 

[Andreas]

On Sat, 23 Jan 2010 08:12:19 -0800 (PST), Andreas


Yes, you are right about the formula.

Let's look at this fragment a bit more:
(select max(myDate) from myTable T where myDate < myTable.myDate) AS
prevDate
Why are we doing this in the first place? Because we need to get the
previous row, and the PK value of that row uniquely describe it. The
idea is to get the maximum date less than the one in the current row.
SQL is a set-based language so the "do this for each row" part is
automatic.
(select...) is a subselect; it allows you to pull in another query on
a different table, or the same table as in this case.
"as prevDate" names the column.
Why use "myTable T"? This is so we can compare this instance of
myTable with the primary instance. Written a bit more verbosely:
(select max(T.myDate) from myTable T where T.myDate < myTable.myDate)
AS prevDate
Perhaps this shows a bit more clearly that there are two instances of
myTable in play.

Thank you very much. This makes it a bit clearer to me.

 

[Al Campagna]

Andreas,
Yes... glad you caught that. My test data was just to "neat" to
show a failure.
Here's what finally worked for me in a query...

Given TestDate and TestAmt:
TestDate TestAmt PrevDate PctGain
1/1/2009 100
2/1/2009 110 1/1/2009 +10.0
3/1/2009 120 2/1/2009 +9.1
4/1/2009 100 3/1/2009 -16.7
5/1/2009 90 4/1/2009 -10.0
6/1/2009 95 5/1/2009 +5.6
7/1/2009 120 6/1/2009 +26.3
8/1/2009 115 7/1/2009 -4.2
9/1/2009 110 8/1/2009 -4.3
10/1/2009 100 9/1/2009 -9.1

PrevDate: DMax("[TestDate]","tblCustomers","[TestDate] < #" & [TestDate] &
"#")

PctGain:
IIf(IsNull([PrevDate]),0,(([TestAmt]/NZ(DLookUp("TestAmt","tblCustomers","TestDate
= #" & [PrevDate] & "#"),0)))-1)*100

Might need a bit of decimal, and Null tweaking, but that should
do it.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Andreas,
Use a calculation in a query...
Reminder: Don't use the object name Date or Value.
It's a reserved word.
Try TransDate and InvValue for example...

Given that all entries are date ascending, use a DMax of the TransDate
for "any Transdate less than the current record's TransDate"
In a query I used my own example. I created a calculated field called
X:
in a query against my table...
X: DMax("[CustID]","tblCustomers","[TestDate] < #" & [TestDate] & "#")

TestDate CustID X
1/1/2009 2
2/1/2009 3 2
3/1/2009 4 3
4/1/2009 5 4
5/1/2009 6 5
6/1/2009 7 6
7/1/2009 8 7
This method could be used to yield the previous InvValue for use in
the % calc, on the current record.

Hi Al,

I am not completely sure, but I think that you'll get a problem as
soon as your CustID is decreasing. If this is the case, you'll carry
on the highest value from the past. Can you please verify?

Andreas

 

[Tom Wickerath]

Hi Andreas,

Check out these two pages, by Access MVP Allen Browne:

Subquery basics
http://www.allenbrowne.com/subquery-01.html

Surviving Subqueries
http://www.allenbrowne.com/subquery-02.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

This is perfectly working. Thanks!
However, as I am an ACCESS beginner, I am just wondering whether you
can explain the SQL code to me with a bit more detail. I would like to
learn and understand it.

<snip>

 

[Andreas]

Hi Andreas,

Check out these two pages, by Access MVP Allen Browne:

     Subquery basics
     http://www.allenbrowne.com/subquery-01.html

     Surviving Subqueries
     http://www.allenbrowne.com/subquery-02.html

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/

Thanks for pointing that out to me. I'll have a look at it.

Andreas