Solved

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

Posted on 2014-11-07
4
645 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How Can I send Access report to email address saved in database? 14 54
ms/access ftp / SFTP 3 32
Syntax Error in Query 7 30
Update fields from multiple tables via same form 2 22
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

932 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

16 Experts available now in Live!

Get 1:1 Help Now