The following works great to generate daily values from a list of sales: That is:
Input Table (Tick)
field1 = TickDateTime
field2 = TickPrice
OutPut Table (Daily) which is stored in a daily table
field = DateOnly
field = OpenPrice
field = HighPrice
field = LowPrice
field = ClosePrice
'***
'Query To Compress
'Tick to Daily Data
'***
strParm = "PARAMETERS [pBegDate] DateTime, [pEndDate] DateTime; "
strSQL = strParm & "SELECT Format(fldTickDateTime,""mm/dd/yy"") As DateOnly, First(fldTickPrice) AS OpenPrice, Max(fldTickPrice) AS HighPrice, "
& "Min(fldTickPrice) AS LowPrice, Last(fldTickPrice) AS ClosePrice "
& "FROM " & TBLName & " "
& "WHERE (((fldTickDateTime) Between [pBegDate] And [pEndDate])) "
& "GROUP BY Format(fldTickDateTime,""mm/dd/yy"");"
QUESTION: Is there anyway to do a GROUP BY query to roll up the daily (output) table data to generate a weekly (i.e. mm/weekending/yy), monthly (ie. mm/yy) and yearly table.
If not, any other suggestions would be appreciated. Currently using a For/Next Loop but find it SLOW.
