Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    Australia
    Posts
    3

    Unanswered: Running total query using date

    Hi,

    I am having trouble with adding criteria to a dsum expression in a query. I need to add criteria to my statement so that I get a running total of payments each payment month by batch month. The end result should look something like this:

    BatchMonth | PaymentMonth | PaymentAmount | RunTot
    2001_01 | 2001_01 | $100 | $100
    2001_01 | 2001_02 | $100 | $200
    2001_01 | 2001_03 | $100 | $300
    .. | .. | .. | ..
    .. | .. | .. | ..
    2001_01 | 2001_12 | $100 | $1200
    2001_02 | 2001_01 | $100 | $100
    2001_02 | 2001_02 | $100 | $200

    At the moment, I have 2 tables, one with data relating to when an outstanding file got sent to an outside agent (fields: BatchDate, Location, AccountNumber, OpeningBalance) and another with payment data on that account (AccountNumber, Location, PaymentDate, PaymentAmount). I have one query summing the payment amount by PaymentMonth and BatchMonth. The SQL for this query is:

    SELECT Format([BatchDate],"yyyy_mm") AS BatchMonth, Format([PaymentDate],"yyyy_mm") AS PaymentMonth, Sum(Payments.PaymentAmount) AS SumOfPaymentAmount
    FROM BatchDetails INNER JOIN Payments ON BatchDetails.AccountNumber = Payments.AccountNumber
    GROUP BY Format([BatchDate],"yyyy_mm"), Format([PaymentDate],"yyyy_mm");

    That query is called xxxDSumExample1. Now I am trying to run a query on that query to create a column with a running total, but I don't know what criteria I need to add to my DSum expression to get the layout above. The SQL for this query is currently:

    SELECT xxxDSumExample1.BatchMonth, xxxDSumExample1.PaymentMonth, xxxDSumExample1.SumOfPaymentAmount, Format(DSum("SumOfPaymentAmount","xxxDSumExample1" ),"$0,000.00") AS RunTot
    FROM xxxDSumExample1;

    Can you help?

    Thanks, Bart
    Last edited by bjesman; 09-09-02 at 22:38.

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    There is an article on the Microsoft Knowledge base that might help.

    http://support.microsoft.com/default...;en-us;Q138911

    David

  3. #3
    Join Date
    Sep 2002
    Location
    Australia
    Posts
    3
    Originally posted by DJN
    There is an article on the Microsoft Knowledge base that might help.

    http://support.microsoft.com/default...;en-us;Q138911

    David
    I tried that, it doesn't help with what I want to do. When I try to add criteria the way they have in the article, it does not work. I need to know specifically what criteria to add to my dsum expression.

  4. #4
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    I'm not sure that you can do what you want in an SQL query.

    If you are using the query to produce a report, you can create a RunningSum in a column which will give you exactly what you want. Set the RunningSum property to 1 and group the report on BatchMonth.
    Roger Hampson
    XI - ecs (UK) Ltd

  5. #5
    Join Date
    Sep 2002
    Location
    Australia
    Posts
    3
    Originally posted by xiecsuk
    I'm not sure that you can do what you want in an SQL query.

    If you are using the query to produce a report, you can create a RunningSum in a column which will give you exactly what you want. Set the RunningSum property to 1 and group the report on BatchMonth.
    Doing it in a report doesn't help because my headings are constantly changing and a report has fixed headings. I know it can be done and "David" above is on the right track, that article does explain how to do it, but unfortunately, it does not work when I try to do it with dates....

  6. #6
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    A report doesn't necessarily have to have fixed headings. I often produce reports which have a varying number of columns, all unbound, which I resolve in the Report Open event by interrogating a recordset which will tell me how many columns I have and what they are called. I then set the unbound controls accordingly before the report gets to the data.
    Last edited by xiecsuk; 09-12-02 at 16:05.
    Roger Hampson
    XI - ecs (UK) Ltd

Posting Permissions

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