Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-11-07
4
Medium Priority
?
1,707 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 2000 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 34

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 39

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 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