VBA Best Practice, Create New Calculated Column

[ilcaa]

I have a table called quotes, it has 3 columns, qDate, qTicker, qClose. this table contains 2 million records. I want to create a new column in this table (5DayReturn) that calculates a value based on a record set. here is an example of the table

qDate, qTicker, qCloseCLOSE, 5DayReturn
5/15/2014, GOOG, 265.25,
5/14/2014, GOOG, 268.75,
5/13/2014, GOOG, 262,50,
5/12/2014, GOOG, 265.00,
5/11/2014, GOOG, 263,90,
5/10/2014, GOOG, 259.00,

i want to loop each row and take the current qClose and the next 5 qClose to calculate a return: (qClose(5)-qClose(0))/qClose(0).

My plan is to loop each row, use the current rows qDate qClose, calculate the value then place it in the row, ill do this for each 2 million rows.

Is this the best way to accomplish this task? Any advise on code or best way to do this?

here is the SQL select query

SELECT TOP 6 qClose, qDate FROM Quotes WHERE qTicker = rs.Fields("qClose") and qDate >= rs.Fields("qDate") ORDER BY qDate;

Sub CalculateReturn

Dim rs as RecordSet
Dim i as Long
Dim db as Database

Set db = Currentdb
Set rs = db.OpenRecordSet("Quotes")

for i = 0 to rs.recordcount
ticker = rs.fields(qTicker")
currDate = rs.Fields("qDate")

query = SELECT TOP 6 qClose, qDate FROM Quotes WHERE qTicker = rs.Fields("qClose") and qDate >= rs.Fields("qDate") ORDER BY qDate;

' Loop recordset, grab first/last value calculate return assign to variable

'assign return variable to new field..

next i

end sub

 

[ranman256]

you dont do this in code.
You do it using a query
add the extra field there.

 

[ilcaa]

not sure what you mean...
If i "create" a query, then it will be stand alone query and not part of the original table. Or are you saying create a "Make Table" query this new field included? But then I would have 2 tables with 2 millions rows each ( i could delete the original table). Thing is, I will do this every week... is this the best option? Thanks for your input

 

[Micron]

In case ranman doesn't reply, he means it is bad practice to store a calculated value in a table. You create a calculated field in a query and use that query in whatever manner of display is required. This could be as simple as a datasheet, or it could be a report or form. If one of the values used in the calculation ever changes, the stored result is incorrect.

However, if by "and the next 5 qClose " you mean the first calculation is to be based on qClose in rows 1, 2, 3, 4, 5 and 6, then I do not know how to do that in a calculated query field. There are aggregate functions you can use in a calculation such as Min, Max, First, Last, etc. but if there is one that groups by x rows beyond the current row, it escapes me. If that is what you are doing, then the complexity is up a few notches. You would have to weigh the difficulty of not storing the value against the impact that an incorrect calculation would cause. Perhaps you could live with the risk and mitigate it by deleting the table records after closing a report or form. Otherwise, you might have to loop through a record set using inner and outer loops where you perform the calculation as you've indicated, whereupon you'd base the form's record source on that recordset object.