Running Total in a Query with a criteria

[accessjockey]

Hi all,

I am trying to create a running total field within a query that has a criteria as well.

Here is the query in design view:

This is the result when the query is run if I use the project number 001;

I would like to create a separate field that calculates the running total. What is the expression I need to write to make this happen?

Thank you in advanced.

 

[Ron_dK]

Try changing the query to something like this :

SELECT Sum(This Invoice) AS SumOfThisInvoice
FROM [Invoices];

 

[accessjockey]

Thanks Ron for your prompt reply.

The code you suggest simply sums all the values within the field 'ThisInvoice' in the 'Invoices' table. I am looking to generate a cumulative total for the records retrieved by the query named 'RunningTotal' and its criteria (ie. List all invoices by the same project number)

How is the DSum code written to make this happen? DSum( ????, ????, ????)

 

[ajetrumpet]

DSUM only sums the COLUMN in question. If you put that function in a separate field in the query, it will give you the sum of the column in question for EVERY record in the query. You don't want that. If you're wanting a TOTAL at the BOTTOM of the field that you want to sum, there is a built-in function for that in Access. It is in on the toolbar somewhere for 2007, but not sure for 2003. If you want a RUNNING total, as it relates to the progressive totaling of the records, that is more complicated.

 

[accessjockey]

Thank you for your reply.

The query retrieves only the invoices that have the same project number by using this criteria- Like"*"&[Enter the Project Number]&"*" - which prompts the user for the project number when the query is run. (See the design view of the query above)

All I am after is a column that shows the running total of the invoices retrieved by the query. It would look like this:

What is the code to be placed in the field named 'Running Total' for this to happen?

 

[ajetrumpet]

I personally think this is incredibly complicated to do. Maybe you can figure this out. Look at the attachment. It all works in my sample except the last record's data. I leave that to you... If you figure it out, let me know!

 

[accessjockey]

Thanks for trying to help. However, the code only creates a running total of the entire main table and not the results created by the query.

I managed to get the same results using the DSum function but was unable to make it work when I placed user imputed criteria within the query. ie. Like"*"&[Enter the Project Numbe]&"*"r

I am disappointed that access cannot perform a simple task such as a running total other than within a report.

All I am after is the ability to ask access to extract an invoice that was generated some time ago (by imputing the criteria of[Project Number] &[Invoice Number])and show me a running total (or sum off all the invoices up until the one being looked at)for the same project.

I am going to take a look at the use of subforms within a main form and see if that works.