Solved

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

Posted on 2014-11-07
4
534 Views
Last Modified: 2014-11-13
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
0
Comment
Question by:Sivasan
  • 2
4 Comments
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40429818
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]
FROM Table2 AS t
ORDER BY t.item, t.[Date Req];


+ 0 is optional. With it, the column displays like a number (right-justified).
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40429823
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];
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40432984
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?
0
 

Author Closing Comment

by:Sivasan
ID: 40440762
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
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now