Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Francois

  3. #3
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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?
    Charlotte

  5. #5
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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".
    Francois

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Charlotte

  10. #10
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •