How to get difference of value of two consicutive dates ?

[Shalini Bhalla]

i have a table in which i store a value on daily basis. so, i have 30 values for a month. Now i want to get %variation in values on daily basis. Please tell me the function to be used and how in m.access

[PianoMan64]

i have a table in which i store a value on daily basis. so, i have 30 values for a month. Now i want to get %variation in values on daily basis. Please tell me the function to be used and how in m.access

Shalini,

You're going to have to provide more information as to what kind of table structure, queries, forms, and what controls on the forms that you have defined.

Since we don't know anything about what you're trying to do, any examples of what you have, and what you want the end result to be, would be greatly appreciated.

Thanks,

Joe P.

[Shalini Bhalla]

i have a table

comp_mst having fields as
comp_name
sym PK



price_vol having fields as

id PK
sym
date
vol
price
`
now i will be storing volume and price values of all companies daily basis.In a query i want to have daily % variation of the volume and price.
till now i have created one query to find out previous date using dateadd()
then 2nd query to find out corresponding value
and i have 3rd one which is simply picking value from price_vol and query to get diffrence.but in this query i am getting cartision product.can you pls guide me

[ADezii]

The following code will calculate the Percentage Differential (+/-) between Volumes of successive Records based on the price_vol Table structure. Calculations for Price Differential would be very similar. The results can very easily be written to a Table instead of being dumped to the Debug Screen:

Dim MyDB As DAO.Database
Dim rst_1 As DAO.Recordset
Dim rst_Clone As DAO.Recordset
Dim strPVar As String
Dim strBig As String
 
Set MyDB = CurrentDb()
Set rst_1 = MyDB.OpenRecordset("price_vol", dbOpenDynaset)
Set rst_Clone = rst_1.Clone       'Exact Duplicate of rst_1
 
If rst_1.RecordCount = 0 Then Exit Sub
 
rst_1.MoveFirst
rst_Clone.MoveFirst: rst_Clone.MoveNext     'Move to 2nd Record
 
Debug.Print "Volume 1     Volume 2    %Variance"
Debug.Print "----------------------------------"
 
With rst_1
  Do While Not rst_Clone.EOF
    'Calculate the Percent Variation
      If rst_Clone![vol] >= ![vol] Then     '2nd > 1st
        strPVar = "+" & Format(((rst_Clone![vol] - ![vol]) / ![vol]), "Percent")
      Else
        strPVar = "-" & Format(((![vol] - rst_Clone![vol]) / ![vol]), "Percent")
      End If
        strBig = "  " & Format$(![vol], "0000") & "         " & _
                        Format$(rst_Clone![vol], "0000") & _
                        "       " & strPVar
        Debug.Print strBig
    .MoveNext
    rst_Clone.MoveNext
  Loop
End With
 
rst_1.Close
Set rst_1 = Nothing
rst_Clone.Close
Set rst_Clone = Nothing

price_vol Data

ID    sym    date            vol    price
1     45     6/2/2008        100    $125.00
2     46     7/14/2008       200    $137.00
3     47     1/29/2008       125    $117.00
4     48     12/12/2008      400    $150.00
5     49     11/16/2008      500    $234.00
6     50     3/3/2008        600     $97.00
7     51     9/21/2008       400    $112.00
8     52     5/31/2008       800    $298.00
9     53     9/2/2008        327    $200.00
10    54     4/12/2008      1000    $213.00
11    888    11/23/2008     1000    $323.00

OUTPUT

Volume 1     Volume 2    %Variance
----------------------------------
  0100         0200       +100.00%
  0200         0125       -37.50%
  0125         0400       +220.00%
  0400         0500       +25.00%
  0500         0600       +20.00%
  0600         0400       -33.33%
  0400         0800       +100.00%
  0800         0327       -59.13%
  0327         1000       +205.81%
  1000         1000       +0.00%
 

[Shalini Bhalla]

thanks alot for the wonderful solution.Actually i have got the correct ans using query also.I have one more question as i am not very much good in access.
I want to put a button on my form to perform events.but when i try to put it , it doesn't show the wizard for events , nor any of the control is showing events in there property .what could be the problem ?

[ADezii]

thanks alot for the wonderful solution.Actually i have got the correct ans using query also.I have one more question as i am not very much good in access.
I want to put a button on my form to perform events.but when i try to put it , it doesn't show the wizard for events , nor any of the control is showing events in there property .what could be the problem ?

Make sure you click on the Control Wizards Button on the Toolbox in order to activate it prior to drawing any Controls on your Form. It is the 2nd Button Horizontally from the Upper Left Corner, and its Icons consists of an Ellipses (...) as well as a Magic Wand (\).