running totals in a query 1

[njb]

does anybody know how to show running totals in a query?<br>
<br>
example:<br>
<br>
name---date----amt----balance <br>
item1 3/1/2000 $10.00 $10.00<br>
item2 3/2/2000 $4.00 $14.00<br>
item3 3/8/2000 ($2.00) $12.00<br>
item4 3/3/2000 $5.00 $17.00

 

[DougP]

Its a lot easier to make report from your query and do totals in there.<br>
In a report you can have Groups and then put a subtotal in the group section and then a Grand total in the Report Footer or Page Footer.<br>
<br>
In your report Click &quot;View&quot; menu then &quot;Sorting and Grouping&quot;<br>
Pick a field or fields that you want to group on and examine each option there for greater control over your report.<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[LWoods]

Try this and see if it doens't give you what you want:<br>
<br>
Create a module with the following code:<br>
<br>
Option Explicit...etc.<br>
<br>
Dim curTotal as Currency<br>
<br>
Public Function CTotal( curIn as Currency ) as Currency<br>
curIn=curIn + curTotal<br>
CTotal=curIn<br>
End Function<br>
<br>
Public Function CTotalZero()<br>
curTotal=0<br>
End Function<br>
<br>
Now, create your query as planned and add two calculated fields:<br>
<br>
CTot: CTotal([Amt])<br>
<br>
and <br>
<br>
CTotSet: CTotalZero()<br>
<br>
The only problem is that you will have to Show the CTotSet field...and it will be 0.00 in each resulting row. You can strip this later.<br>
<br>
The purpose of CTotSet is to assure that you start with a zero total each time that you run the query. If you don't show the row, CTotSet will not execute...and the total will accumulate between query executions.<br>
<br>
Larry Woods<br>
<br>

 

[njb]

Thanks for the replies. I know I can do this as a report, but that wasn't what I was trying to do - good reply though. I tried LWoods approach but I can't seem to get it to work. CTot returns the same value as AMT. And CTotSet shows a blank column. Any thoughts? <br>

 

[njb]

I finally did get this work with some minor modifications to LWoods approach. First, I created the query (without the running totals) and then attached it to a form. Then created the two functions, but changed the code in function CTotal to:<br>
<br>
Public Function CTotal(curIn As Currency) As Currency<br>
<br>
curTotal = curIn + curTotal<br>
CTotal = curTotal<br>
<br>
End Function<br>
<br>
Then added an unbound text box to the form and assigned &quot;=ctotal([amount])&quot; to the Control Source. Also changed to the forms &quot;On Current&quot; event to call function CTotalZero. Works great!<br>
<br>
Thanks again for all the help!!!!!<br>
<br>
<br>
<br>