Return Calculation from Previous Records on Same Day/Date

[CJ_London]

try

SELECT *, (high-low)*-(SELECT COUNT(*) FROM myTable Tmp WHERE Date=myTable.Date AND Time<=myTable.Time AND Change>0.0169)>0 AS [Count Jump]
FROM myTable

[CJ_London]

try

SELECT *, abs((high-low)*(SELECT COUNT(*) FROM myTable Tmp WHERE [Date]=myTable.[Date] AND [Time]<=myTable.[Time] AND Change>0.0169)>0) AS [Count Jump]
FROM myTable

remember my advice re using reserved words like date and time as field names - it will cause you problems at some point - better to change them to something more meaningful such readdate/readtime

[mshannonjones69]

Ajax,

I have made a new update query and put your code in as follows:

SELECT *, abs((High-Low)*(SELECT COUNT(*) FROM Daily Data Tmp WHERE [Date]=Daily Data.[Date] AND [Time]<=Daily Data.[Time] AND Change>0.0169)>0) AS [ChangeJump]
FROM Daily Data

However, I'm getting a syntax error that just says, "Syntax error in query expression 'abs((High-Low)*(SELECT COUNT(*) FROM Daily Data Tmp WHERE [Date]=Daily Data.[Date] AND [Time]<=Daily Data.[Time] AND Change>0.0169)>0)'.

[mshannonjones69]

Bulzie,

I made a button in a new form and put this in the Visual Basic editor, however the button doesn't seem to do anything when I push it. I checked a place that should have more Jumps in the data, and there is nothing:

rs1 = Select * from Table ORDER BY Symbol, Date, Time Asc
vChangeFlag = 0
vSymbol = Null
vDate = Null
rs1.MoveFirst
Do Until rs1.EOF
If rs1!Symbol = vSymbol and rs1!Date = vDate then
If rs1!Change > 0.0169 OR vChangeFlag = 1 then
rs1.Update
rs1!ChangeJump = rs1!High - rs1!Low
rs1.Update
vChangeFlag = 1 (setting this means that all records after this one for same Stock and Date will need to do the calculation)
End If
Else
vChangeFlag = 0 (Set Flag to 0 on new Symbol and Date)
If rs1!Change > 0.0169 then (This part processes the first record in the table or record with new Stock and Date)
rs1.Update
rs1!ChangeJump = rs1!High - rs1!Low
rs1.Update
vChangeFlag = 1 (setting this means that all records after this one for same Stock and Date will need to do the calculation)
End If
vSymbol = rs1!Symbol
vDate = rs1!Date
End IF
rs1.MoveNext
Loop

[Bulzie]

Sorry, the code I gave is not all formatted with the right declarations, I was just giving the logic. Might need to add:

Dim dbs as Database, rs as Recordset

Set dbs = CurrentDb
rs1 = "Select * from Table ORDER BY Symbol, Date, Time Asc"
Set rs = dbs.OpenRecordset(rs1, dbOpenDynaset, dbSeeChanges)

Put breakpoints in to step through code using F8 to test things out.

[CJ_London]

However, I'm getting a syntax error that just says, "Syntax error in query expression 'abs((High-Low)*(SELECT COUNT(*) FROM Daily Data Tmp WHERE [Date]=Daily Data.[Date] AND [Time]<=Daily Data.[Time] AND Change>0.0169)>0)'.

you have spaces in your table names, so you need to use square brakcets there as well. (again, spaces in table and field names is not a good idea)

you proably want to order it as well

SELECT *, abs((High-Low)*(SELECT COUNT(*) FROM [Daily Data] Tmp WHERE [Date]=[Daily Data].[Date] AND [Time]<=[Daily Data].[Time] AND Change>0.0169)>0) AS [ChangeJump]
 FROM [Daily Data]
ORDER BY [Date], [Time]

[mshannonjones69]

Ajax,

No syntax error now, but I tried to run the query twice and it locked up Access both times - Not Responding. I wonder what in this code might do that?

[mshannonjones69]

Ok. Will have to defer to my wife on your solution this evening. I'm not savvy enough to mess with this. ;-o

[CJ_London]

could be your date and time fields - as explained they are reserved words. Both are functions which return todays date and the current time respectively.

You'll need to clarify 'locked up' - you mean the query was taking a long time or you were getting a message such as 'Acces has stopped responding'?

If you have a large amount of data and it is not indexed, it can take some time. The fields that need to be indexed are Date, Time.

Also, I see your times are virtually every minute - which implies around 3600 records per day, so would not take long for this to become a very big table.

Suggest try filtering on a single day to see the results for one day.

Also, still not sure whether your field 'Symbol' is relevant - if this is a company identifier then presumably you would want your calculation basis to be related to only that company - at the moment if your table contains data relating to a number of companies, the calc is based on 'if any company has a change >=0.0169 and before time.

Other things - you can combine your date and time fields into one field (just add them together), this reduces the number of indexes required and the number of criteria comparisons that need to be done.

[CJ_London]

Just realised you actually do have a datetime field but on reflection it would not improve performance, however did find a calc error so this is a revised query

SELECT *, (High-Low)*-((SELECT COUNT(*) FROM [Daily Data] Tmp WHERE [Date]=[Daily Data].[Date] AND [Time]<=[Daily Data].[Time] AND Change>0.0169)>0) AS [ChangeJump]
 FROM [Daily Data]
ORDER BY [Date], [Time]

[Bulzie]

Ajax not sure if that is going to get them the logic they need. From an earlier post from Msh:

At 11:10, the CHANGE met our criteria of being greater than 0.0169 and the CHANGE JUMP calculated [High]-[Low]. We want the Change Jump calculation to continue to occur until the end of the day after 11:10.
This is all stock specific.

Looks like your where clause pulls records that are same Date, Time and a Change > 0.0169? But once they find the first record that needs a Change Jump calculation, every record after that needs to do the same Change Jump calculation, even if the Change is not > .0169. Is that correct Msh?

Sorry I might not be interrupting your Select statement right and maybe it does do that, just asking.

[CJ_London]

the result produced from the query is this

which is what I believe to OP requires. Although still not clear about the symbol requirement - but query can be easily modified to

SELECT *, (High-Low)*-((SELECT COUNT(*) FROM [Daily Data] Tmp WHERE [Symbol]=[Daily Data]. [Symbol] AND [Date]=[Daily Data].[Date] AND [Time]<=[Daily Data].[Time] AND Change>0.0169)>0) AS [ChangeJump]
 FROM [Daily Data]
ORDER BY [Date], [Time]

Symbol would also need to be indexed for performance reasons

[mshannonjones69]

Bulzie,

Yes, once a dip is found, we would want every 1 minute increment (record) to calculate the Change Jump, regardless.

Also, yes there is a Symbol (stock) requirement. I guess we didn't make that perfectly clear.

[Bulzie]

Nice statement Ajax. Can you explain the parts of it so I can learn something new? So everything starting with (High-Low) to the end is used to calculate the ChangeJump value for each record? and basically it is High-low * a negitive value from the inbedded Select Count statement? How does it know to include records in the middle that do not meet the Where clause? I know I am missing something here...

SELECT *, (High-Low)*-((SELECT COUNT(*) FROM [Daily Data] Tmp WHERE [Symbol]=[Daily Data]. [Symbol] AND [Date]=[Daily Data].[Date] AND [Time]<=[Daily Data].[Time] AND Change>0.0169)>0) AS [ChangeJump] FROM [Daily Data] ORDER BY [Date], [Time]

[CJ_London]

-((SELECT COUNT(*) FROM [Daily Data] Tmp WHERE [Symbol]=[Daily Data]. [Symbol] AND [Date]=[Daily Data].[Date] AND [Time]<=[Daily Data].[Time] AND Change>0.0169)>0)

the select statement counts the number of records with same symbol, same date and time same as or earlier than the time in the 'current' record in the main query. Same time is required to include the current record. if it is greater than 0 the comparison with 0 (>0) will return true (i.e. -1), or if 0 it return false (i.e. 0) so multiply the 'current' record high-low by 0 or --1 (i.e. +1).

Tmp is an alias to distinquish the daily data table in the subquery from the daily data table in the main query

I tend to alias in my own work all the time - if this was for me I would alias the main table as C (for current) and in the subquery P (for previous) for example - means clarity on using same table more than once in a query (subquery or not), reduces typing (particularly for long table names) and if a calculation in a query is complex, makes it much easier to read.

For me, using booleans in this way makes for cleaner, simpler code - in vba instead of something like

if thisvalue=thatvalue then
    acontrol.visible=true
else
    acontrol.visible=false
end if

I would use

acontrol.visible=thisvalue=thatvalue

Hope that helps