By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,556 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

How to get difference of value of two consicutive dates ?

100+
P: 190
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
Sep 28 '08 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 374
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.
Sep 28 '08 #2

100+
P: 190
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
Sep 28 '08 #3

ADezii
Expert 5K+
P: 8,669
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst_1 As DAO.Recordset
  3. Dim rst_Clone As DAO.Recordset
  4. Dim strPVar As String
  5. Dim strBig As String
  6.  
  7. Set MyDB = CurrentDb()
  8. Set rst_1 = MyDB.OpenRecordset("price_vol", dbOpenDynaset)
  9. Set rst_Clone = rst_1.Clone       'Exact Duplicate of rst_1
  10.  
  11. If rst_1.RecordCount = 0 Then Exit Sub
  12.  
  13. rst_1.MoveFirst
  14. rst_Clone.MoveFirst: rst_Clone.MoveNext     'Move to 2nd Record
  15.  
  16. Debug.Print "Volume 1     Volume 2    %Variance"
  17. Debug.Print "----------------------------------"
  18.  
  19. With rst_1
  20.   Do While Not rst_Clone.EOF
  21.     'Calculate the Percent Variation
  22.       If rst_Clone![vol] >= ![vol] Then     '2nd > 1st
  23.         strPVar = "+" & Format(((rst_Clone![vol] - ![vol]) / ![vol]), "Percent")
  24.       Else
  25.         strPVar = "-" & Format(((![vol] - rst_Clone![vol]) / ![vol]), "Percent")
  26.       End If
  27.         strBig = "  " & Format$(![vol], "0000") & "         " & _
  28.                         Format$(rst_Clone![vol], "0000") & _
  29.                         "       " & strPVar
  30.         Debug.Print strBig
  31.     .MoveNext
  32.     rst_Clone.MoveNext
  33.   Loop
  34. End With
  35.  
  36. rst_1.Close
  37. Set rst_1 = Nothing
  38. rst_Clone.Close
  39. Set rst_Clone = Nothing
price_vol Data
Expand|Select|Wrap|Line Numbers
  1. ID    sym    date            vol    price
  2. 1     45     6/2/2008        100    $125.00
  3. 2     46     7/14/2008       200    $137.00
  4. 3     47     1/29/2008       125    $117.00
  5. 4     48     12/12/2008      400    $150.00
  6. 5     49     11/16/2008      500    $234.00
  7. 6     50     3/3/2008        600     $97.00
  8. 7     51     9/21/2008       400    $112.00
  9. 8     52     5/31/2008       800    $298.00
  10. 9     53     9/2/2008        327    $200.00
  11. 10    54     4/12/2008      1000    $213.00
  12. 11    888    11/23/2008     1000    $323.00
OUTPUT
Expand|Select|Wrap|Line Numbers
  1. Volume 1     Volume 2    %Variance
  2. ----------------------------------
  3.   0100         0200       +100.00%
  4.   0200         0125       -37.50%
  5.   0125         0400       +220.00%
  6.   0400         0500       +25.00%
  7.   0500         0600       +20.00%
  8.   0600         0400       -33.33%
  9.   0400         0800       +100.00%
  10.   0800         0327       -59.13%
  11.   0327         1000       +205.81%
  12.   1000         1000       +0.00%
  13.  
Sep 28 '08 #4

100+
P: 190
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 ?
Sep 28 '08 #5

ADezii
Expert 5K+
P: 8,669
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 (\).
Sep 28 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.