How do you calculate rate of change in microsoft access?

[Guest]

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

 

[Al Campagna]

Joanthan,

I know how to calculate the actual rate of change in normal practice and
in
excel

Would you care to share that Excel calculation with us? (with sample
values, and correct results)
--
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."

 

[Guest]

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

 

[Al Campagna]

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."

 

[Guest]

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

 

[Al Campagna]

Well, whatever works... works.
You could try both methods, and see if you get better from one or the other.
Good Luck
--
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,

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."

message

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

:

Joanthan,
I know how to calculate the actual rate of change in normal practice
and
in
excel
Would you care to share that Excel calculation with us? (with
sample
values, and correct results)
--
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."

message
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