DSUM (?) help

[unit213]

I'm having trouble creating a running sum in a query in Access 2007. Please see below for a (rudimentary) example of what I'm trying to accomplish. How would I go about doing this in a query?

Any help you can offer would be great.

Thanks.

Dan

 

[xenou]

Running totals are not really part of SQL queries generally speaking. You *can* do this in an Access report. You can also push/pull the query to/from Excel and use Excel to do the running total.

I guess if you really wanted to you could also write a short script to fill in the running totals and drop it into a temporary table as a data source.

ξ

 

[alansidman]

Xenou is correct, however I have two workarounds that could be employed. While the data bases that are linked below are not exactly what you are looking for, you can extract the concepts and probably develop your own workaround.

In the checkbook database, I created a running sum in a query that adds up all the ins and outs based upon the date and then in an expression subtracts the withdrawals from the deposits.

In the automileage database, I created an expression that gets the previous mileage for the record preceding the current record. I then create an expression to subtract this value from the new mileage.

Look them over and I am sure you will develop your own workaround.

Here are links to the two files.

http://www.box.net/shared/yhj15r8r9dty6oikfyck

http://www.box.net/shared/yaj30ia91jbfo2c2ehr8

Alan

 

[unit213]

Thanks for the help Xenou. I appreciate it.

In the checkbook database, I created a running sum in a query that adds up all the ins and outs based upon the date and then in an expression subtracts the withdrawals from the deposits.

First and foremost, that you very much for the sample databases to review. I have a quick question for you. I see your logic in the checkbook database, but can you please explain what the ' Select Nz' statement does (or how it works) in the expression below:

Balance: (select Nz(Sum([Deposits] -[Withdrawals] ),0) from tblTransactions WHERE tblTransactions.TranDate <=tbl.TranDate)

It appears to grab the beginning balance from the tblTransactions table, which is very similar to what I'm looking for. I just haven't seen that before.

Thanks!

Dan

 

[alansidman]

The NZ statement basically says, "if a record in the field shown is null, then take the value after the comma and use that." This ensures that Access can do a calculation. If Access finds a null record then it says that it can't do a calculation. This precludes that from happening. To make the running sum work, you have to create an alias table based upon either a date or a unique id. The select statement here defines the criteria for that.

Look at this link for further explanation: http://www.mrexcel.com/forum/showthread.php?t=358307&highlight=Running+Sums

Alan

 

[unit213]

The NZ statement basically says, "if a record in the field shown is null, then take the value after the comma and use that." This ensures that Access can do a calculation. If Access finds a null record then it says that it can't do a calculation. This precludes that from happening. To make the running sum work, you have to create an alias table based upon either a date or a unique id. The select statement here defines the criteria for that.

Look at this link for further explanation: http://www.mrexcel.com/forum/showthread.php?t=358307&highlight=Running+Sums

Alan

Thanks again. I'm making good progress. I'll work on this more tomorrow.

 

[Marbles]

Allen Browne's article on Subqueries might help:

http://allenbrowne.com/subquery-01.html