Running Total in Query

[accessjockey]

Hi all,

I would like to add a column to this query that calculates a running total of the 'This Invoice' field.

Here is the query design :

This query will return results based on information provided by the user.

Here is the result if I type in 001 as the project number I would like to look at:

I have attempted to use the DSum function. It works fine however, it completly ignores the parameter criteria of the query and returns a running total of the entire 'Invoices' table. I would like it to return a running total of only the figures relating to the project number I asked it to look at.

Here is the code I used:

RunTot: DSum("ThisInvoice","Invoices","[InvoiceNumber]<=" & [ID] & "")

I would like the 'RunTot' calculated field to return results like this:

What do I need to add to restrict the calculation to the results of the query?

 

[MStef]

You have to go via Function.
Look at "DemoRuningtotalA2000.mdb" (attachment, zip).
Look at Table1, query1, Form1 (VBA), Module1.
Open Form1 and try.
I suggest make a report on query1.

 

[accessjockey]

Thanks for that.

The example you have given me works great however, when I recreate the query using the field names of my table it fails miserably.

Is there any reason why I cannot simply add to the criteria within the DSum function I have already created? It currently produces a running sum of the entire Invoices table. All I need it to do is only RunSum the values produced by the query it exists in.

accessjockey

 

[MStef]

It must be you made a mistake somewhere.

 

[accessjockey]

Hi,

Yes you're correct. I didn't export the module to my databse. Once I did that it worked. However!

The running total just keeps on adding to the last result of the query. Every time I fire up the query the running total adds the results of the last query to one I have just generated. Even if it is a totally different job number. Not good accounting practice.

This is getting way to difficult for such a simple piece of arithmetic. All I need is the sum of one particular field up until the current date for one particular project.

The running sum works great within a report however this function falls down when the group of records are not the same job number. For example, a report that looks at invoices that are 30days overdue.

The current Dsum function I have already created (see above) calculates the running total fantastically. It just needs to be given a further criteria restricting it to the project number that is given by the user when the query is run.

Why is that such a difficult thing?

 

[MStef]

You have said;
"The running total just keeps on adding to the last result of the query. Every time I fire up the query the running total adds the results of the last query "

Look at Form1 VBA, i think you omited A2 = 0 code.