Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / July 2007

Tip: Looking for answers? Try searching our database.

How do you calculate rate of change in microsoft access?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JonathanLewin - 17 Jul 2007 13:12 GMT
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 - 17 Jul 2007 14:18 GMT
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)
Signature

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

>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
[quoted text clipped - 8 lines]
>
> Thanks
JonathanLewin - 18 Jul 2007 09:10 GMT
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
[quoted text clipped - 14 lines]
> >
> > Thanks
Al Campagna - 18 Jul 2007 15:15 GMT
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.
Signature

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
[quoted text clipped - 62 lines]
>> >
>> > Thanks
JonathanLewin - 18 Jul 2007 22:50 GMT
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.
[quoted text clipped - 87 lines]
> >> >
> >> > Thanks
Al Campagna - 19 Jul 2007 05:32 GMT
Well, whatever works... works.
You could try both methods, and see if you get better from one or the other.
Good Luck
Signature

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,
>
[quoted text clipped - 110 lines]
>> >> >
>> >> > Thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.