446,392 Members | 1,556 Online
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
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

 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 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 Expand|Select|Wrap|Line Numbers 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 Expand|Select|Wrap|Line Numbers 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%   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

 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