Finding Records with increased values (1 Viewer)

My table has Product, Date, and Value on it. Each month the table gets updated with all current Products and their Value so one Product may be listed on the table 50 times. Most times the Value will be the same or slightly lower.

I am trying to query the table to catch times when the Value increased from the previous Date. I am looking to capture all 3 fields when this occurs.

Sample:
Product A, 10/01/2017, 100
Product A, 11/01/2017, 99.5
Product A, 12/01/2017, 100
Product B, 10/01/2017, 101
Product B, 11/01/2017, 101
Product B, 12/01/2017, 100

I would be looking to capture Product A, 12/01/2017, 100 because this row had the value increase from 99.5 to 100. I would not want to return any other rows for Product A because they decreased in Value. I wouldn't want any rows for Product B because they either decreased in Value or remained the same.

Any thoughts on how I can query the table to get the results I am looking for? Let me know if you have any questions. Thanks.

First thought is using something like this to get the current and previous on the same row, then using a criteria on that.

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

Queries can't compare values of adjacent rows.

Cycle through the table or query, row by row, with DAO or ADO code, comparing the current row against the previous row.

This is going to take at least one subquery. Start with a new query and bring in every field you want to show. Then make a calculated field that obtains the prior date for each record.

Let's call this field 'LastDate'. You can determine its value via a correlated sub query (https://msdn.microsoft.com/en-us/li...v=office.11).aspx?f=255&MSPPError=-2147217396) or you can do this using a DMax (https://www.techonthenet.com/access/functions/domain/dmax.php)

Save that query (let's call it 'sub1') and then build a new one using it and your table. You JOIN them via Product fields and then the Date field to LastDate. Bring down Product, Date and Value from sub1 and make a calculated field. This field will be sum1.Value minus your table's Value field. In the criteria area of that calculated field put '>0'.

Run it and that's the data you want.

My table has Product, Date, and Value on it

Click to expand...

If that statement is literally true, then a word of advice: Both DATE and VALUE are Access Reserved Words (the names of properties). You should not use them as field names. If those are not the actual field names and you were being generic, disregard this comment.

Thanks all. The subquery technique seemed to get me what I needed. I appreciate all of your help.