1. ## Calculating (2002)

A newbie to Access and would like to know how to do month-to-date calcs. I input daily figures in one column and would like to have a running total in the next.

Thanx.

Kim

2. ## Re: Calculating (2002)

Kim,

I don't understand what you want to do, but the DateDiff function is something you can use for date calculations. Look in the help file for it.

3. ## Re: Calculating (2002)

I want to have running totals on daily data entries. I do not want to manipulate dates.

If data is entered on 12/3 for a number of 20 and on 12/4 for a number of 30, I would like to accumulate the data in another column so that the accumulated column would show 20 for 12/3 and 50 for 12/4.

Thanx.

Kim

4. ## Re: Calculating (2002)

You don't normally do that in a table, you do it in a query. It's bad design to store calculated data like this. How did you want to use this information?

5. ## Re: Calculating (2002)

Thank you for your response. I believe the earlier thread said that I want to do a month to date calculation on a daily basis. I do not think I said where this manipulation was to be done whether it is in a query, table, report, form or on a notepad.

Here is the form of the data to be calculated: I will enter the daily data into a table. Then somewhere else I would like to add todays value to yesterdays value to create a MTD (month-to-date) so that after the month has expired there would be two columns reporting data, one would be input, the other calculated.

Any help would be greatly appreciated.

Thanx

Kim

6. ## Re: Calculating (2002)

Yes, you have expanded on your original post, but it is still unclear as to whether you want to simply display the month-to-date in a form or report (you haven't specified which) or you want to store it in a table. What exactly are you trying to do? Unlike a spreadsheet, Access doesn't offer you a simple way to say "sum the values in that column from this row to that row". Without knowing exactly what you want to accomplish beyond displaying a cumulative value, it's hard to give you a useful answer.

7. ## Re: Calculating (2002)

You said that it is 'bad practice' to store calculated output in a table. Therefore, it seems to me that the alternative is to use it in either a form or a report which is what I want to do.

8. ## Re: Calculating (2002)

I don't know how to do this in a form or query without writing code.

In a report, put the amount field twice in the detail section and for the second one set the Running Sum property to "Over All" or to "Over Group".

9. ## Re: Calculating (2002)

Here's some "air" SQL to do it in a query:

SELECT MyTable.CustID, MyTable.TransDate, MyTable.DailyAmt,
Val(nz(DSum("[DailyAmt]","MyTable","[CustID]=" & [CustID] & " AND [TransDate]<=#" & [TransDate] & "#") ,0) AS SumToDate
FROM MyTable
ORDER BY MyTable.CustID, MyTable.TransDate;

This assumes you have a table called MyTable and that it has at least a CustID field to identify the customer, a TransDate field to give you the date of the amount, and a DailyAmt field with a value in it. The calculated SumToDate will return the sum of the transactions for that CustID up to and including the current TransDate. You could substitute a subquery for the DSum as well.

10. ## Re: Calculating (2002)

Another way of describing what I want to do is a running sum. If possible, I would prefer to do it in a query so as it can be exported to VB.

Thanx.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•