Hi, I have a problem with a query and I hope someone can help. I have a simple table with three fields (StockID, MonthEnd (a date), and TotalReturn). The TotalReturn is the percentage gain or loss over that month. Currently I intend to hold over 10 years of data for each stock. I am looking for an output that will be able to be ranked that shows rolling results for certain periods. i.e. if I have 2 years of data I could show 12 rolling one-year positions. I don't really want to dump to a table but if I must then ok. The problem I have is that the first record needs a slightly different calc than the rest so it is not a simple running total. So far I have used a defined function in VBA which is a little flaky as it keeps recalc'ing on its own (MS ack this) prob due to static defs. What I have is below and may well be the wrong route to go but ...

Query 1
PARAMETERS NoOfMonths Short;
SELECT tblTotalReturn.StockID, tblTotalReturn.MonthEnd AS OrigME, Count(tblTotalReturn_1.StockID) AS CountOfStockID
FROM tblTotalReturn INNER JOIN tblTotalReturn AS tblTotalReturn_1 ON tblTotalReturn.StockID = tblTotalReturn_1.StockID
WHERE ((([tblTotalReturn_1]![MonthEnd]<=[tblTotalReturn]![MonthEnd] And [tblTotalReturn_1]![MonthEnd]>DateAdd("m",-[NoOfMonths],[tblTotalReturn]![MonthEnd])+3)=True))
GROUP BY tblTotalReturn.StockID, tblTotalReturn.MonthEnd
HAVING (((tblTotalReturn.StockID)=1) AND ((Count(tblTotalReturn_1.StockID))>=[NoOfMonths]))
ORDER BY tblTotalReturn.StockID, tblTotalReturn.MonthEnd;

Query 2
SELECT tblTotalReturn.StockID, Query1.OrigME, tblTotalReturn.MonthEnd, tblTotalReturn.TotalReturn, FormatNumber(fncRunCalc([Query1]![StockID],[Query1]![OrigME],[tblTotalReturn]![TotalReturn]),4) AS X
FROM Query1 INNER JOIN tblTotalReturn ON Query1.StockID = tblTotalReturn.StockID
WHERE ((([tblTotalReturn]![MonthEnd]<=[Query1]![OrigME] And [tblTotalReturn]![MonthEnd]>DateAdd("m",-[NoOfMonths],[Query1]![OrigME])+3)=True))
ORDER BY Query1.OrigME, tblTotalReturn.MonthEnd;

Query 3
SELECT Query2.StockID, Query2.MonthEnd, Query2.X
FROM Query2
WHERE (((Query2.MonthEnd)=[OrigME]));

This comes out with the wrong answers even though Query 1 & 2 are right.

Function fncRunCalc(lngStockID As Long, dtMonthEnd As Date, sinTR As Single) As Single
'Variables that retain their values.
Static dtDate As Date
Static sinPos As Single
Static lngID As Long
'dtDate = 0
'sinPos = 0

If lngID <> lngStockID Or dtDate <> dtMonthEnd Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngStockID
dtDate = dtMonthEnd
sinPos = 100 + sinTR
Else
'If the current ID matches the last, keep a running sum for the ID.
sinPos = sinPos * (1 + (sinTR / 100))
End If

'Pass the running sum back to the query.
fncRunCalc = sinPos
End Function

Many thanks in advance.

Hey there,
Can you post a copy of your DB with some sample data?

Hi. I'm not permitted yet to upload stuff but here is some data.

StockID MonthEnd TotalReturn
1 31/07/2010 6.764374
1 31/08/2010 -8.271735
1 30/09/2010 9.938603
1 31/10/2010 4.572426
1 30/11/2010 -0.400534
1 31/12/2010 10.52279
1 31/01/2011 -6.003639
1 28/02/2011 0.7419355
1 31/03/2011 -4.290746
1 30/04/2011 -6.289729
1 31/05/2011 2.891824
1 30/06/2011 -2.463567
1 31/07/2011 -4.55354
1 31/08/2011 -20.79762
1 30/09/2011 -11.67059
1 31/10/2011 9.536494
1 30/11/2011 -7.587549
1 31/12/2011 -4.736842
1 31/01/2012 1.98895
1 29/02/2012 2.654388
2 31/07/2010 -0.8264463
2 31/08/2010 0
2 30/09/2010 -2.5
2 31/10/2010 -3.418803
2 30/11/2010 -3.539823
2 31/12/2010 0
2 31/01/2011 0
2 28/02/2011 0
2 31/03/2011 0.9174312
2 30/04/2011 1.818182
2 31/05/2011 0
2 30/06/2011 -1.339286
2 31/07/2011 0
2 31/08/2011 -0.4524887
2 30/09/2011 -1.818182
2 31/10/2011 0
2 30/11/2011 6.944445
2 31/12/2011 -1.731602
2 31/01/2012 -0.4405286
2 29/02/2012 1.327434
3 31/07/2010 -1.327434
3 31/08/2010 1.793722
3 30/09/2010 0.3524229
3 31/10/2010 -0.2633889
3 30/11/2010 3.080986
3 31/12/2010 2.818104
3 31/01/2011 -2.906977
3 28/02/2011 2.053037
3 31/03/2011 -1.760268
3 30/04/2011 -1.194539
3 31/05/2011 6.044905
3 30/06/2011 -1.465798
3 31/07/2011 -0.1652893
3 31/08/2011 0.8278146
3 30/09/2011 -0.6568145
3 31/10/2011 -0.5785124
3 30/11/2011 0.5818787
3 31/12/2011 -0.8264463
3 31/01/2012 8.333334E-02
3 29/02/2012 1.082431
4 31/07/2010 3.827282
4 31/08/2010 -0.3780718
4 30/09/2010 10.05693
4 31/10/2010 0.6896552
4 30/11/2010 0.5136986
4 31/12/2010 7.325383
4 31/01/2011 -0.6349207
4 28/02/2011 -4.792332
4 31/03/2011 2.013423
4 30/04/2011 -0.9046053
4 31/05/2011 -2.572614
4 30/06/2011 -0.8517888
4 31/07/2011 5.841924
4 31/08/2011 -10.87662
4 30/09/2011 -5.100182
4 31/10/2011 7.485605
4 30/11/2011 -6.607143
4 31/12/2011 1.912046
4 31/01/2012 12.57036
4 29/02/2012 5.666667
5 31/07/2010 0.6722689
5 31/08/2010 0.6677796
5 30/09/2010 6.467662
5 31/10/2010 1.869159
5 30/11/2010 -0.9174312
5 31/12/2010 5.092593
5 31/01/2011 -2.790015
5 28/02/2011 -6.344411
5 31/03/2011 4.677419
5 30/04/2011 5.392912
5 31/05/2011 1.169591
5 30/06/2011 2.890173
5 31/07/2011 -1.896067
5 31/08/2011 -0.9305655
5 30/09/2011 -8.598266
5 31/10/2011 7.114625
5 30/11/2011 -4.354244
5 31/12/2011 3.780864
5 31/01/2012 4.535316
5 29/02/2012 3.271693
6 31/07/2010 5.172414
6 31/08/2010 -0.8196721
6 30/09/2010 21.4876
6 31/10/2010 9.523809
6 30/11/2010 -1.863354
6 31/12/2010 17.08861
6 31/01/2011 0
6 28/02/2011 -17.2973
6 31/03/2011 5.882353
6 30/04/2011 26.54321
6 31/05/2011 -7.317073
6 30/06/2011 2.105263
6 31/07/2011 2.835052
6 31/08/2011 -7.26817
6 30/09/2011 -2.702703
6 31/10/2011 2.777778
6 30/11/2011 -4.864865
6 31/12/2011 0.5681818
6 31/01/2012 16.94915
6 29/02/2012 16.90821
7 31/07/2010 7.375271
7 31/08/2010 5.959596
7 30/09/2010 18.58913
7 31/10/2010 1.768489
7 30/11/2010 -4.423381
7 31/12/2010 6.115703
7 31/01/2011 -7.398754
7 28/02/2011 -7.905803
7 31/03/2011 12.23744
7 30/04/2011 2.929211
7 31/05/2011 3.083004
7 30/06/2011 2.147239
7 31/07/2011 1.088589
7 31/08/2011 1.002599
7 30/09/2011 -12.35294
7 31/10/2011 4.110738
7 30/11/2011 -4.75423
7 31/12/2011 1.015228
7 31/01/2012 2.051926
7 29/02/2012 9.396799
8 31/07/2010 10.81081
8 31/08/2010 0
8 30/09/2010 -14.63415
8 31/10/2010 14.28571
8 30/11/2010 6.5
8 31/12/2010 -6.103286
8 31/01/2011 6.5
8 28/02/2011 -0.2347418
8 31/03/2011 0
8 30/04/2011 0
8 31/05/2011 0
8 30/06/2011 0
8 31/07/2011 0
8 31/08/2011 0
8 30/09/2011 0
8 31/10/2011 0
8 30/11/2011 0
8 31/12/2011 0
8 31/01/2012 0
8 29/02/2012 0
10 30/09/2010 2.158273
10 31/10/2010 3.99061
10 30/11/2010 -0.6772009
10 31/12/2010 1.363636
10 31/01/2011 -1.569507
10 28/02/2011 -3.302961
10 31/03/2011 0.5889282
10 30/04/2011 2.810304
10 31/05/2011 -0.2277904
10 30/06/2011 0.456621
10 31/07/2011 -3.181818
10 31/08/2011 -3.755868
10 30/09/2011 -14.63415
10 31/10/2011 10
10 30/11/2011 0.7792208
10 31/12/2011 -1.546392
10 31/01/2012 7.329843
10 29/02/2012 2.804878

So just looking at stockid 1 we can have 6 sets of 15m data as there are 20 periods in all. the results should be:

30/09/2011 70.5374
31/10/2011 72.3689
30/11/2011 72.9087
31/12/2011 63.1763
31/01/2012 61.6155
29/02/2012 63.5054

If you start with £100. i.e. first calc is 100 + 6.764374
next line is (prev result) * (1-(TR/100)) or in data terms
106.764374 * -8.27 etc %
by the time it is done for 15 months the results are as above.

Thanks for your interest.

Hope that helps

Sorry the calc should read: (prev result) * (1+(TR/100))