Find High and Low Values

[jalverson]

I'm new to coding, so I'm not even sure where to start. I have a table with prices by day. I want to start with the first day, 1/1/2009 and step through each record in date order. I want to set a flag when the price goes below 11.25, and then count how many days it takes to go above 11.75. Then, reset the flag and count how many days it takes to fall back below 11.25. The end result is to have how many times the price fell below 11.25, and how many times it rises above 11.75 and the average time between each peak and valley. Looking at a graph, the solution looks easy, turning it into code that works is where I need help. I appreciate any responses.

Jeff

 

[Galaxiom]

This can be done with a query. Drag the table into the designer twice. (Join the two tables only on the ProductID if this involves multiple items.) This will make a cartesian join.

Put the price and date fields from each table into the result grid. Set the first price as the bottom level. Set the price criteria on the second price field as the top level.

Set the date criteria of the second date field as as the lowest date that is greater than the first date. Derive a field of the difference between the dates.

This will return the first half of the problem. Once you get your head around this and get it working, go back and add a third table and proceed similarly to achieve the other half of problem.

 

[jalverson]

Your response solved my question. After researching the cartesian join that you mentioned, I had a better understanding on writing the queries. I spent the weekend testing the queries, and they are working perfectly. I appeciate the help.

Jeff