Return Calculation from Previous Records on Same Day/Date

[mshannonjones]

Hello! I'm a newbie, so bear with me please.

My wife and I have an Access database with historical stock data and I need to create an expression for the Change Jump field that updates one of the main tables. Each day has many records from each stock symbol, so it is somewhat difficult because things aren't just 1 item per day, but many records in the same day/Date.

I just need the expression to do this, but I don't know all the parts/syntax to get it done. Everything in brackets is a field:

[Change Jump] = If any records with the same [Date] as this record and where the [Time] is before this record (military/Short format), then calculate the [High]-[Low] of that record and return that number. If not, then return a 0.

Thanks in advance!

 

[mshannonjones]

This is all I have currently, but I know it is not correct:

IIf([Date]="& [Date] &" And [Time]<="& [Time] &" And [Change]>0.0169,[High]-[Low],"0")

 

[pbaldy]

Well, if the poorly named date and time fields are date/time, then perhaps:


IIf([Date]=#"& [Date] &"# And [Time]<=#"& [Time] &"# And [Change]>0.0169,[High]-[Low],"0")

or are you looking for something like:

http://allenbrowne.com/subquery-01.html#AnotherRecord

FYI, I moved your thread out of the introductions forum.