Return Calculation from Previous Records on Same Day/Date

[mshannonjones69]

Ajax,



We think this will work but we there are a couple of things we are uncertain of.

Do we add this into the vba of our main update query (for the Daily Data table) and if so, does it go in place of our current expression to update our Change Jump field or just at the end of the vba of the main update query?

We have run this code in a separate query and it looks like this is creating another field - ChangeJump. Is that correct? If we put this code into our main update query, will it update our Change Jump field or still create a new field?

Thanks!

[CJ_London]

why are you updating? calculated values should not be stored. - The code I provided will not convert to an update query - instead you would need to use the dcount domain function in which case your full query to update the change jump field would be

UPDATE [Daily Data] SET [Change Jump] = ([High]-[Low])*-(DCount("*","[Daily Data]","[Symbol]='" & [Symbol] & "' AND [Date]=#" & [Date] & "# AND [Time]<=#" & [Time] & "# AND Change>0.0169")>0)

, but be aware domain functions do not use indexing so it will be slower

We have run this code in a separate query and it looks like this is creating another field - ChangeJump

not another field - a calculated column

If we put this code into our main update query, will it update our Change Jump field or still create a new field?

it won't create another field. If you want spaces then change AS ChangeJump to AS [Change Jump] - but see code above for the code to run.

[mshannonjones69]

Ajax,

i'm running the new query now. Not trying to be impatient, because we "asked for it" with the slow running domain function and all that... but it has been running for 1 hour now and i'm just wondering if i should forget about it and come back in 4 or 5+ hours, or should it only take an hour or two?

Thank you much!

[CJ_London]

no idea how long it will take. All depends on how many records and whether you have indexed the relevant fields - although domain functions don't use indexing. I guess once you have caught up you will only have a day at a time to do - modify the query to prompt for the day you want or use the default of Date for todays new records.

You might find it better to do in small chunks say 7 days at a time. But as I've said before, you should use the original query to display the values when required rather than storing calculated data.

[mshannonjones69]

Ajax,

To (1) test the update query itself and to also be able to (2) do this in chunks - I have stopped the query from running and modified your SQL to the following:

UPDATE [Daily Data] SET [Daily Data].[Change Jump] = ([High]-[Low])*-(DCount("*","[Daily Data]","[Symbol]='" & [Symbol] & "' AND [Date]=#" & [Date] & "# AND [Time]<=#" & [Time] & "# AND Change>0.0169")>0)
WHERE ((([Daily Data].Date)>=[Start Date] And ([Daily Data].Date)<=[End Date]));

Will the code above work for me to just run a couple of days of data and test the query, so i'm not waiting hours and hours.

Then, we will run chunks of data over the weekend and get us "up to date" - so we can do what you suggested in the future, by downloading data and updating the ChangeJump only several days at a time.

What do you think?

[CJ_London]

is the data indexed? how many records in a day? how many days data? without know this info, impossible to say

the bigger the time period, the longer it will take - suggest do one day and time how long it takes, then do 2 days and compare times - the bigger the period, the longer it will take per day - so if 1 day takes say 1 minute, 10 days might take 12 minutes

I also repeat - something you keep ignoring and could be affecting performance

1. you should not be storing calculated fields, just use the original query when required for whatever it is you are using it for
2. you should not use date and time as field names
3. have you indexed the table?