access divide one record by record uner it

[johnclayton]

table has Date and StockPrice:
12-26-16 100.00
12-25-16 105.00
like to divide 100/105 to see percent increase or decrease? expreesion builder would be great but any help great

 

[Joe4]

Welcome to the Board!

This is not as simple as you might think, because Access is very different than Excel. In a Relational Database like Access, the position of records within a table really has no meaning (someone once said to think of it as a jumbled bag of marbles). This is because in a Relational Database records are supposed to be independent of each other, and position really has no meaning.

So that makes doing this like you mentioned a bit tricky. Here are some links that shows a few ways:
http://www.mrexcel.com/forum/microsoft-access/506608-query-return-previous-date.html
Retrieve Previous Record's Date - Access World Forums
how to use values of previous row in the current row for calculation - UtterAccess Forums

 

[Micron]

Those links seem to be centered around dates whereas your example is not. Perhaps that is not relevant, but I do agree it's not simple for a novice.

It is possible if you create an ordered recordset from a query that sorts the records (for your purpose I presume you'd use ascending order). Assuming you would compare 1 against 2, then 2 against 3, 3 against 4, etc., after creating the DAO recordset you'd have to loop through the records, assign the values to variables (easier than constantly referring to the recordset field) and subtract the differences between the variables. The trick is to move to the next record and restart the loop while assigning the next value to the proper variable and subtract again. Repeat and rinse.

This might be one of those times Excel is the better tool.