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
5 1550
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.
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 8,834
Recognized Expert Expert
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%
-
thanks alot for the wonderful solution.Actual ly 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 8,834
Recognized Expert Expert
thanks alot for the wonderful solution.Actual ly 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 (\).
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: tamilan71 |
last post by:
Hello All
I have table with following fields:
GroupId VisitDate
1 10/19/1993
1 11/24/1998
2 10/18/1993
2 10/29/1998
3 10/21/1993
|
by: Ashish Sheth |
last post by:
Hi All,
In C#, How can I get the difference between two dates in number of months?
I tried to use the Substract method of the DateTime class and it is
giving me the difference in TimeSpan,From which I can get the duration
in days, hours and so.. but how can I get the difference in months?
Please reply ASAP. it's urgent.
--
regards,
Ashish Sheth
|
by: Simon Dean |
last post by:
Probably being a little thick here, but when you subtract one date away
from another, how do you convert the resultant value into a number of
days... I guess I could easily / 60 / 60 / 24... but that seems
barbaric... Anything neater?
Cheers
Simon
Ps, Im also just trying to work out how to calculate the number of
mondays and tuesdays etc between two dates... Just thought I'd run this
|
by: jamesyreid |
last post by:
Hi,
I'm really sorry to post this as I know it must have been asked
countless times before, but I can't find an answer anywhere.
Does anyone have a snippet of JavaScript code I could borrow which
calculated the difference in years and days between two dates, and
takes leap years into account?
I'm calculating the difference in the usual way, i.e....
|
by: Yotam |
last post by:
Hi,
I need some help with JS. I will be grateful, if you can help me out.
I have two date fields (check in, check out) and "number of days"
field.
I want the script to calculate automatically the difference.
For example: I have defaults dates, and I want the script to put the
difference in "number of days" field. And if the user will change the
date, the number of days will change automatically.
| |
by: Blackmore |
last post by:
I am trying to use javascript to calculate the difference between two form inputted dates and return the result to another form object. When I load up the page with the function on my web browser the form does not load and I get a message to say that the page contains errors, presumably as the function is not initialising or being referenced properly. Can anyone help, is the function scripted correctly and are the variables I am attempting to...
|
by: Claudia Fong |
last post by:
Hi,
In VB we have DateDiff to calculate the days difference between 2 dates,
I was wondering if we
have something like that in C#?
I want to calculate for example the days difference betwenn 19/06/2007
and 12/06/2007.. it
|
by: Julius |
last post by:
Hej dudes,
I need to calc the difference between two timestamps / dates ...
For example what i need to calculate:
Date 1: 2007.11.06 - 20:13:04
Date 2: 2007.11.07 - 21:13:04
Difference: 1 day, 1hour
|
by: Mike |
last post by:
I use c#, V2005
How I can get difference between two dates and get value in month(s)
I had found some solutions but it is not exactly what I need.
private static int monthDifference(DateTime startDate, DateTime
endDate)
{
int monthsApart = 12 * (startDate.Year - endDate.Year) +
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |