Link to home
Start Free TrialLog in
Avatar of Sivasan
SivasanFlag for United States of America

asked on

Get the value from previous row and use it current row field in Access Query

Hi I have query in access  where I have a table that has fields Item, Date Req, Required, Available that is grouped by Item, ordered by  Date Req field.
I like to get a field "Previous use" which will be the previous record value from the required field, except when it doesn't have one for the group, item. For example I like to see the result as follows

Item  Date Req     Required   Available   Previous Use    
Abc   11/1/14         100             600             0          
Abc    11/3/14         200            600             100    
Abc    11/10/14        100           600             300         ( 100 +200  which is  before 11/10/14)
CCC    11/2/14          200           700             0
CCC      11/5/14        300           700            200

I will appreciate if somebody can let me know how I can do this access query.
thanks
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The following adds 'Balance' column:

SELECT t.item, t.[Date Req], t.Required, t.Available, (Select nz(sum(tt.Required),0) From Table2 tt where t.Item = tt.Item And tt.[Date Req] <  t.[Date Req] )+0 AS [Previous Use], [Available]-[Previous Use] AS Balance
FROM Table2 AS t
ORDER BY t.item, t.[Date Req];
Avatar of PatHartman
This is an expensive operation in a query.  It is trivial in a report or in a code loop.  What are you doing with the resultset?
Avatar of Sivasan

ASKER

Hi eghtebas,
Thanks a lot for all your help.
PatHartman, thank you for your suggestion, yes, I do see it is an expensive operation. I use Crystal report for the report, so this was better doing it in access query. I ended up saving the query results on a table using make table, this was pulling the data on the report was not heavy.
thank you both