RunningSum in Access97

[vodded]

I am developing a simple stock control/manufacturing planning system using Access97. This is very simple in concept but I have hit a snag. <br><br>The plan is to enter stock in to stores, and stock out of stores for each of a number of products with date of transaction. From this I planned to run a query to produce an on-screen form representing a stock card - when the form is opened the query requests the product ID and the query then calculates the running total for each entry line. <br><br>The basic design comprises a single table as follows:<br><br>EntryID (autonumber), ProductID, Transaction Date, Stock in, Stock out.<br><br>The query consists of :<br>EntryID (autonumber), ProductID, Transaction Date, Stock in, Stock out,&nbsp;&nbsp;Line EndlQty, RunningSum<br><br>Line EndQty =[Stock in]-[ Stock out]<br>RunningSum is calculated using DSum i.e&nbsp;&nbsp;&nbsp;&nbsp;RunTot: DSum(&quot;EndQty&quot;,&quot;BatchesQuery4&quot;,&quot;[ID]&lt;=&quot; & [ID] & &quot;&quot<br><br>The problem is it only works for consecutive records, and if I add a criteria to enable me to enter the ProductID an error is shown in the RunningSum field. (The methodology is based on an Access Application Note which also states the above limitation.) <br><br>I would appreciate some guidance to get around this problem. I can E-mail the basic program if this would assist.

 

[JaSintoHenriqueSido]

u can use an subquery

1º select your data from principal query
2º now u have consecutives records
3º use another query in principal query
4º use your formula in the second query and u will have
what you want [sig][/sig]