You are here:

Using MS Access/Access 2007 use previous record total

Advertisement


Question
QUESTION: "Hello, Yes I did read the article and tried to use it. I could not get it to work. I found this article ACC2000: Referring to a Field in the Previous Record or Next Record use some ideas from it and was able to get what I have now. The query is giving me see below.
RecContainerID… RptProdDateRomeoRec.. Container20ftRec…Container20ftEmpty..Container20ftWaitProcess
11…1dec2013…10…..4……6…..should be…6
12…2dec2013……5…..2….3…..should be….9
13.3dec2013.…13….9….3…..should be….13
14…4dec2013…..8….5…..3…..should be….16
15…5dec2013….6…..5…..1…..should be….17
16…6dec2013….4…..3….1…..should be…..18
17…7dec2013….3…..8…..-5…..should be….13

What guidance can you provide?"

ANSWER: The article says to use a Dsum. Its pretty specific. Can you show me the SQL for your query using the articles instructions?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA


---------- FOLLOW-UP ----------

QUESTION: Sir, I am using Dsum. This is the query below. The results in the previous email are the results from the below query.
What am I doing wrong? I am book taught, experience level about 8 months.

SELECT tblRomeoRecContainer.RecContainerID, tblRomeoRecContainer.RptProdDateRomeoRec, tblRomeoRecContainer.Container20ftRec, tblRomeoRecContainer.Container20ftEmpty, Dsum("(container20ftrec-container20ftempty)","tblromeoreccontainer","[RecContainerID]=" & [RecContainerID]) AS container20ftwaitprocess
FROM tblRomeoRecContainer
GROUP BY tblRomeoRecContainer.RecContainerID, tblRomeoRecContainer.RptProdDateRomeoRec, tblRomeoRecContainer.Container20ftRec, tblRomeoRecContainer.Container20ftEmpty, tblRomeoRecContainer.RecContainerID
ORDER BY tblRomeoRecContainer.RptProdDateRomeoRec, IIf(Container20ftEmpty>Container20ftRec,0,1), tblRomeoRecContainer.RecContainerID;

Answer
Ok, try it this way. First create a query just to calculate the wait process.

SELECT RecContainerID, RptProdDateRomeoRec, Container20ftRec, Container20ftEmpty, container20ftrec-container20ftempty AS container20ftwaitprocess
FROM tblRomeoRecContainer;

Call this qryWaitProcess.

Now use this to do your running sum,

SELECT RecContainerID, RptProdDateRomeoRec, Container20ftRec, Container20ftEmpty, Dsum("container20ftwaitprocess","qryWaitProcess","[RecContainerID] <= " & [RecContainerID]) AS RunSumwaitprocess
FROM qryWaitProcess
GROUP BY RecContainerID, RptProdDateRomeoRec, Container20ftRec, Container20ftEmpty
ORDER BY tblRomeoRecContainer.RptProdDateRomeoRec, tblRomeoRecContainer.RecContainerID;

Definitely using = in the DSum was wrong, because it only sums the current record, you have to sum the current and PRIOR records. And, in fact the article specifically says to use <= not =. It does help if you follow the instructions.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.