Solved

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

Posted on 2014-11-07
4
1,059 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 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 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 37

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

738 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