Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2008
    Posts
    54

    Unanswered: Problem with Running Sum

    I have a table “table1” with the following fields:

    Chemicals InvDate Price Qty
    Calcium 13/02/2008 $45 2
    Copper 20/03/2008 $40 1
    Calcium 20/06/2008 $46 1
    Copper 10/01/2008 $40 1
    Calcium 06/08/2008 $45 1

    I would like to do a Running Sum on its quantity (called CumQty) after grouping the chemicals

    The result I expect to see is
    Chemicals InvDate Price Qty CumQty
    Calcium 13/02/2008 $45 2 2
    Calcium 20/06/2008 $46 1 3
    Calcium 06/08/2008 $45 1 4
    Copper 10/01/2008 $40 1 1
    Copper 20/03/2008 $40 1 2

    I used 2 queries to achieve my objective.
    SELECT Table1.Chemicals, Table1.InvDate,Table1.Price, Table1.Qty
    FROM Table1
    ORDER BY Table1.Chemicals, Table1.InvDate;


    SELECT Query1.Chemicals, Query1.InvDate, Query1.Price, Query1.Qty, DSum("Qty","Query1","Chemicals='" & [Chemicals] & "' and InvDate<= " & [InvDate] & "") AS CumQty
    FROM Query1;

    The result is blank for CumQty

    Then I tried with DSum("Qty","Query1","Chemicals='" & [Chemicals] & "' and InvDate<=# [InvDate] #") and got error.

    Please help me to correct the DSum

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Your first effort is closer, but the date value needs to be surrounded by # (your second effort didn't concatenate the value):

    General: DLookup Usage Samples
    Paul

  3. #3
    Join Date
    Jan 2008
    Posts
    54
    SELECT Query1.Chemicals, Query1.InvDate, Query1.Price, Query1.Qty, DSum("Qty","Query1","Chemicals='" & [Chemicals] & "' and InvDate <= #" [InvDate] "#") AS CumQty
    FROM Query1;

    Syntax error before I could even run the query

    SELECT Query1.Chemicals, Query1.InvDate, Query1.Price, Query1.Qty, DSum("Qty","Query1","Chemicals='" & [Chemicals] & "' and InvDate <= # [InvDate] #") AS CumQty
    FROM Query1;

    CumQty gets #error

    In fact I have tried many combinations after looking at some examples, I still could not figure it out.

    Please tell me where is the mistake.

  4. #4
    Join Date
    Mar 2011
    Posts
    24
    SELECT Query1.Chemicals, Query1.InvDate, Query1.Price, Query1.Qty FROM Query1 WHERE DSum("Qty","Query1","Chemicals='" & [Chemicals] & "' and InvDate <= #" [InvDate] "#") AS CumQty;


    sql is:
    SELECT <something> FROM <table> WHERE <condition>

  5. #5
    Join Date
    Jan 2008
    Posts
    54
    Thank you for your reply.

    My query2 now reads " SELECT Query1.Chemicals, Query1.InvDate, Query1.Price, Query1.Qty FROM Query1 where DSum("Qty","Query1","Chemicals='" & [Chemicals] & "' and InvDate <= #" [InvDate] "#") AS CumQty; "

    I also check Access 2003 DSUM in Query. Both are similar and should be right.

    But the the result I obtained was
    Chemicals InvDate Price Qty CumQty
    Calcium 13/02/2008 $45.00 2 2
    Calcium 20/06/2008 $46.00 1 3
    Calcium 06/08/2008 $45.00 1 2 < -- Should be 4
    Copper 10/01/2008 $40.00 1 2 < -- Should be 1
    Copper 20/03/2008 $40.00 1 2
    Sodium 01/07/2008 $47.00 2 <-- Why blank
    Sodium 02/07/2008 $48.00 4 < -- Why blank
    Sodium 15/07/2008 $47.00 3 9

    What is happening?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You have the quoting right but forgot the & to concatenate the date value in.
    Paul

  7. #7
    Join Date
    Jan 2008
    Posts
    54
    Thank you Paul and Engelienart.
    My query2 now reads:

    SELECT Query1.Chemicals, Query1.InvDate, Query1.Price, Query1.Qty, DSum("Qty","Query1","Chemicals='" & [Chemicals] & "' and InvDate <= # " & [InvDate] & "#") AS CumQty
    FROM Query1;


    However the result is not what I intended.
    Chemicals InvDate Price Qty CumQty
    Calcium 13/02/2008 $45.00 2 2 <-- Correct
    Calcium 20/06/2008 $46.00 1 3 <-- Correct
    Calcium 06/08/2008 $45.00 1 2 <-- Should be 4
    Copper 10/01/2008 $40.00 1 2 <-- Should be 1
    Copper 20/03/2008 $40.00 1 2 <-- Correct
    Sodium 01/07/2008 $47.00 2 <-- Should be 2
    Sodium 02/07/2008 $48.00 4 <-- Should be 6
    Sodium 15/07/2008 $47.00 3 9 <-- Correct

    I am puzzled with the result though I think the code should work. Can anyone of you explain the problem?
    Attached Files Attached Files

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It is working correctly for me. I'd guess it's a problem with the non-US date format. See if this works for you:

    CumQty: DSum("Qty","Query1","Chemicals='" & [Chemicals] & "' and InvDate <= # " & format([InvDate], "mm/dd/yyyy") & "#")
    Paul

  9. #9
    Join Date
    Jan 2008
    Posts
    54
    You are a gem, Paul. It works perfectly! Many thanks.

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Ah good, glad we got it sorted out.
    Paul

Posting Permissions

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