How do you calculate rate of change in microsoft access?

[JonathanLewin]

I have design and built a database that uses oil sampleing data which
displays various graphs i.e. amount of hydrogen against dates. i need to
calculate the daily RATE OF CHANGE of the data.

I know how to calculate the actual rate of change in normal practice and in
excel but i do not know how to tell Access to calculate the rate of change
for each record by using the previous record.!?

If any one can help me with this matter i would be very grateful !?

Thanks

 

[JonathanLewin]

Al Campagna,
To my understanding to calculate the rate of change between two points the
standard rate of change formula is used: (Change in Y) / (Change in X).

In Excel if you have the dates in a column i.e. " A " and the values in a
column i.e. " B " in this case these two columns hold the dates the sample
where taken and the amount of Hydrogen present. so to calculate the rate of
change between consecutive records you can use the formula of:

=(B8-B7)/(A8-A7)

If this formula is copied down the list with the numbers increasing in
increments of one then it will calculate the rate of change per day per for
each record.

Example:

A B C
Date Hydrogen Rate of Change
6
7 14/07/2003 20
8 17/09/2004 27 =(B8-B7)/(A8-A7) This returns 0.02 of
Hydrogen per day
9 28/10/2005 38 =(B9-B8)/(A9-A8) This returns 0.03 of
Hydrogen per day
10
......etc......

(The Rate of change has been set to 2 d.p so increasing this would give more
accurate results)

This then shows the rate of change between each record over a period of time
allowing you to see how the rate of change has "changed" over time.

To my understanding this is correct, if you or anyone else knows otherwise
please let me know, and if you can solve my initial question using this
information then I would be thankful.

Jonathan

 

[JonathanLewin]

Al Campagna,

Thank you very much for your help with this, i have come up with an idea of
my own which is also not the most elegant way of carrying out the job but i
am confident it works and gives me the result i need. I will implement your
suggestion into my database and see which one is the most "elegant for my
database.

Thank you very much again for your assistance it is much appreciated.

Jonathan

Jonathan,
You really should have a autonumber key field for each record.
In this example, I'll use your numbers (6, 7, 8, etc) as if it were
the [SampleID]
Your Date field "appears" to be providing that uniqueness, but
there's always the possibilty of problems with that...

This is just the "concept"...
If we we're on the record where SampleID = 9... and on some event...

Dim PrevID as Long
Dim PrevDate as Date
Dim PrevHyd as Integer
1. Use DMax to find the SampleID that is one less than the current
SampleID
( varPrevID = 8)
2. Use Dlookup to find the SampleDate where SampleID = 8
(varPrevDate = 17/09/2004)
3. Use Dlookup to find the Hydrogen value of SampleID = 8
(varPrevHyd = 27)

RateOfChange = (Me.SampleDate - varPrevDate) / (Me.Hydrogen - PrevHyd)

There may be more elegant ways to accomplish this... but this should do
it.
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

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

Al Campagna,
To my understanding to calculate the rate of change between two points the
standard rate of change formula is used: (Change in Y) / (Change in X).

In Excel if you have the dates in a column i.e. " A " and the values in a
column i.e. " B " in this case these two columns hold the dates the sample
where taken and the amount of Hydrogen present. so to calculate the rate
of
change between consecutive records you can use the formula of:

=(B8-B7)/(A8-A7)

If this formula is copied down the list with the numbers increasing in
increments of one then it will calculate the rate of change per day per
for
each record.

Example:

A B C
Date Hydrogen Rate of Change
6
7 14/07/2003 20
8 17/09/2004 27 =(B8-B7)/(A8-A7) This returns 0.02 of
Hydrogen per day
9 28/10/2005 38 =(B9-B8)/(A9-A8) This returns 0.03 of
Hydrogen per day
10
......etc......

(The Rate of change has been set to 2 d.p so increasing this would give
more
accurate results)

This then shows the rate of change between each record over a period of
time
allowing you to see how the rate of change has "changed" over time.

To my understanding this is correct, if you or anyone else knows otherwise
please let me know, and if you can solve my initial question using this
information then I would be thankful.

Jonathan