DSUM FUNCTION

[VC2002]

Hi!

I have an access table with a date and price column.

I want to do a rolling 15 day sum of the prices in a query of the table, i.e. for every day, sum the previous fifteen days prices.

I think the DSUM function in the Expression Builder is what I want, but I'm unsure how to actually build the expression.

All help greatly appreciated!

Thanks...

 

[PHV]

A starting point:
SELECT A.Date, A.Price, (SELECT Sum(B.Price) FROM yourTable B WHERE B.Date Between A.Date-15 AND A.Date) AS Rolling15
FROM yourTable A;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VC2002]

Hi PH,

Should I write your select statement in VB or in the expression builder?

Thanks,

VC

 

[PHV]

In the SQL pane of a query window.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VC2002]

Getting there!

It's returning a constant value, not summing the previous 15 days each day.

I think one final question: I don't understand where "yourTableB" and therefore "B.Date" and "B.Price" comes from.

I've only got the one table with a "Date" and "Price" column. Is "yourTableB" the query?

Thanks again for all your help!

 

[VC2002]

The value it's returning in the 'Rolling15' column is actually the sum of the entire price column, and not the previous 15 days price data...

 

[PHV]

Can you please post the sql code you tried ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VC2002]

Sure, it's:


SELECT tblPriceData.Date,tblPriceData.Close, (SELECT Sum(qryPriceData.Close) FROM qryPriceData WHERE qryPriceData.Date Between tblPriceData.Date-15 AND tblPriceData.Date) AS Rolling15

FROM tblPriceData;


Thanks,

 

[PHV]

And this ?
SELECT A.Date, A.Close, (SELECT Sum(B.Close) FROM tblPriceData AS B WHERE B.Date Between A.Date-15 AND A.Date) AS Rolling15
FROM tblPriceData AS A;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244