DSum with Dynamic Range

[Kimbertha]

Hello Everyone:

I have a query with a DSum that calculates a running sum for a field named [Log]. The query is sorted by date, and the sum ascends accordingly. So that works really well.

Now I need to calculate a DSum for the interval between two dates. For example, I want to enter a parameter of >12/31/2005 and <01/01/2008 and have the Dsum calculate a running sum only on the values for [Log] between this interval.

Right now when I enter the parameter, the query returns the records between the interval, but gives the Dsum as if calculated on every record in the query.

Here is the SQL:

SELECT StockChange.PriorTradeDate, StockChange.TradeDate AS DateAlias, StockChange.PriorTradeClose, StockChange.TradeClose, StockChange.TradeChange, Sum(StockChange.Log) AS SumOfLog, Round(DSum("[Log]","[StockChange]","[TradeDate]<=#" & [DateAlias] & "#"),9) AS Dsum, Round(DStDev("[Log]","[StockChange]","[TradeDate]<=#" & [DateAlias] & "#"),9) AS StDev, Round([StDev]*(252^0.5),4) AS [LT Mean Volatility]

FROM StockChange

GROUP BY StockChange.PriorTradeDate, StockChange.TradeDate, StockChange.PriorTradeClose, StockChange.TradeClose, StockChange.TradeChange



HAVING (((StockChange.TradeDate)>#12/31/2005# And (StockChange.TradeDate)<#1/1/2008#));

Any ideas what I am doing wrong?

Regards,

Kim

[pbaldy]

Is this what you want?

DSum("[Log]","[StockChange]","[TradeDate]<=#" & [DateAlias] & "# AND [TradeDate]>#12/31/2005#")

[Kimbertha]

Hello P!

I inserted the code but I don't think I did it correctly. The query runs, but the DSum column appears blank in the results. However the good news it that the query runs, so I can't be too far off on the syntax.

New SQL:

SELECT StockChange.PriorTradeDate, StockChange.TradeDate AS DateAlias, StockChange.PriorTradeClose, StockChange.TradeClose, StockChange.TradeChange, Sum(StockChange.Log) AS SumOfLog,
Round(DSum("[Log]","[StockChange]","[TradeDate]<=#" & [DateAlias] & "# AND [TradeDate]>#12/31/2005#"),9) AS Dsum,
Round(DStDev("[Log]","[StockChange]","[TradeDate]<=#" & [DateAlias] & "#"),9) AS StDev, Round([StDev]*(252^0.5),4) AS [LT Mean Volatility]

FROM StockChange

GROUP BY StockChange.PriorTradeDate, StockChange.TradeDate, StockChange.PriorTradeClose, StockChange.TradeClose, StockChange.TradeChange;

In case you haven't already figured it out, I am a MSMD Certified Programmer, aka Monkey See, Monkey Do.

Kim

[pbaldy]

LOL at MSMD! It worked with the existing DSum? Can you post the db, or a sample of it?

[Kimbertha]

Hey P:

I'll a sample later today . . . the database is at work.

I am hoping once I can do a dynamic Dsum, and I can use it as follows:

Table1
Has a column called [Grants]. They have a [GrantDate] (the date when they were granted) and an [ExerciseDate] (the date when they were cashed in). The span between the two dates can range from a year to up to 10 years, depending on what the grant owner wants to do. There are about 13,000 records in this file.

Table2
Has a list of calendar dates called [Date] that start at 01/01/2000 and runs to the present. There is also a column called [Log] that has numeric values. There are about 3,000 records in this file.

Query1
For each [Table1].[Exercise] I want to get the Dsum of the [Table2].[Log] values that fall between the [Table1].[GrantDate] and the [Table1].[ExerciseDate]. When I have that, I need to take the standard deviation of the Dsum and do some other math on the standard deviation.

Output
A make-table or query that displays the values from Table1 joined with the calculated standard deviation values from the Query1. So this table will have the 13,000 records from Table1 plus the values pulled in from Query1.

So the tricky part is getting the dynamic Dsum, assuming this is the best way to logically structure the query.

I have this working in a query with a static range, that is, the entire range of dates from [Table2], the Dsum, the standard deviation, etc. But I can't figure out how to make the Dsum dynamic so that only the records between the [Table1].[GrantDate] and [Table1].[ExerciseDate] are pulled into the Dsum, etc. for each specific [Table1] record. The way my query works now, I can pull the standard deviation value for the [Table1].[ExerciseDate], but it's calculated on the interval between [Table2].[Date] starting at 01/01/2000 through the [Table1].[ExerciseDate].

A picture is worth a thousand words, so I'll post a sample later

Regards,

Kim

[Kimbertha]

Hello P (and any others who may be reading):

Here is as close as I cam come on posting the DB without violating the client's confidentiality rules . . . the numbers are made up but I couldn't strip the client's name from the Security tab on the DB properties.

The table:



Data

PriorTradeDateTradeDateLog1/3/20001/4/200091/4/20001/5/200081/5/20001/6/200071/6/20001/7/200061/7/20001/10/200051/10/20001/11/200041/11/20001/12/200031/12/20001/13/200021/13/20001/14/200011/14/20001/18/200091/18/20001/19/200081/19/20001/20/200071/20/20001/21/200061/21/20001/24/200051/24/20001/25/200041/25/20001/26/200031/26/20001/27/200021/27/20001/28/200011/28/20001/31/200051/31/20002/1/20005




The queries look like this:


DSum no Date Parameter
SELECT Data.PriorTradeDate, Data.TradeDate AS DateAlias, Sum(Data.Log) AS Log, Round(DSum("[Log]","[Data]","[TradeDate]<=#" & [DateAlias] & "#"),9) AS Dsum, Round(DStDev("[Log]","[Data]","[TradeDate]<=#" & [DateAlias] & "#"),9) AS StDev, Round([StDev]*(252^0.5),4) AS [LT Mean Volatility]
FROM Data
GROUP BY Data.PriorTradeDate, Data.TradeDate;

Comment: This query works great. The math is correct in the DSum column, which is a running sum over the entire population.


DSum with Date Parameter

SELECT Data.PriorTradeDate, Data.TradeDate AS DateAlias, Sum(Data.Log) AS Log, Round(DSum("[Log]","[Data]","[TradeDate]<=#" & [DateAlias] & "#"),9) AS Dsum, Round(DStDev("[Log]","[Data]","[TradeDate]<=#" & [DateAlias] & "#"),9) AS StDev, Round([StDev]*(252^0.5),4) AS [LT Mean Volatility]
FROM Data
GROUP BY Data.PriorTradeDate, Data.TradeDate
HAVING (((Data.TradeDate) Between [Enter Starting Date] And [Enter Ending Date]));

Comment: This query does not calculate the running sum correctly based on the date parameter. Instead it grabs the entire population prior to the starting date and uses that in the running sum calculation.

If it's too messy to deal with the above, I'll put together a little DB when I get home tonight using the above data and post that example.
As I mention in my post earlier today, what I'm really trying to do is get a dynamic caluation based on the start and end dates for 13,000 records. (These 13,000 records are not represented in the above examples.) For each record I want to reach over to the above DB and grab the appropriate running sum and do the DSum and StDev for that record's interval. I figure if I can get the Dsum to work dynamically based on date parameters, I can kluge together the rest in my typical MSMD fashion.

Thanks for reading!

Kim

[ajetrumpet]

I am a MSMD Certified Programmer

how much does that cost to get? do I have to go to the local training center?

[Kimbertha]

Perhaps a change of logic is in order.

What if I created a Form that does the Dsum and calculations. Then for each record (13,000 of them), the form would do the calculation and return a value that would be displayed in a query? Can Forms do this sort of thing?

I am a complete newbie with Forms as my DBs always rely on imported data and never manually entered data.

Hmmmm. Must read up on Forms . . .

[pbaldy]

Were you not able to put together a sample db?

[Kimbertha]

Here is the sample! : )

There are two queries. The DSum works fine when there is no date parameter.

When there is a date parameter the Dsum ignores the parameter and does the calculation on the entire range. Then the query displays only the records within the date parameter. I have something wrong, but I cannot tell what it is.

Kim

[Kimbertha]

See attached.

[pbaldy]

Does this do what you want?

Dsum1: Round(DSum("[Log]","[Data]","[TradeDate]<=#" & [TradeDate] & "# AND TradeDate >=#" & [Enter Starting Date] & "#"),9)

[Kimbertha]

Yes! Yes! Yes! . . . I think this is it ! ! ! !

I'll play this through tomorrow and post a SOLVED. P is a Wizard.

Kim

[pbaldy]

Happy to help Kim!

[Kimbertha]

Nevermind! I stared at it for a while even read-up in my "Access: The Missing Manual" book. It's starting to make sense.

Credit to PBaldy as the solution came from him.

========

Okay I give up . . .

What is the syntax to add and ending date parameter? I thought I could get it by messing around with your statement, but I don't have it right.

Solution with Beginning Date Parameter

Dsum1: Round(DSum("[Log]","[Data]","[TradeDate]<=#" & [TradeDate] & "# AND TradeDate >=#" & [Enter Starting Date] & "#"),9)

Modified Solution with Begining and Ending Date Parameter

Dsum1: Round(DSum("[Log]","[Data]","[TradeDate]<=#" & [TradeDate] & "# AND TradeDate >=#" & [Enter Starting Date] & "# AND TradeDate <=#" & [Enter Endnig Date] & "#"),9) AS Dsum

But my modified solution doesn't stop summing at the end date