Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cumulative Totals in a Query (A2002 SP3)

    Hi,

    My archive search mainly found references to the RunningSum property which can be used on reports, but I want to return the equivalent cumulative totals in a query. I found a similar need discussed under post 399565 where the DCOUNT function was used.

    My table tblProductDetails contains the following fields : TestID, ProductIndex, ProductMass, and some example data is :

    TestID ProductIndex ProductMass
    1 1 100
    1 2 50
    1 3 20
    1 4 10
    2 1 80
    2 2 40
    2 3 20
    2 4 10
    2 5 5
    2 6 2

    I would like to add a column to my query to return the cumulative totals :

    CumProductMass
    100
    150
    170
    180
    80
    120
    140
    150
    155
    157

    I have tried the following, but I am missing something obvious as it returns the grand total for all tests and products for each record.

    SELECT TestID, ProductIndex, ProductMass,
    DSum("[tblProductDetails]![ProductMass]", "[tblProductDetails]",([tblProductDetails]![ProductIndex]<=[ProductIndex] And [tblProductDetails]![TestID]=[TestID])) AS CumProductMass
    FROM tblProductDetails;

    I also tried a slightly different DSUM formula : DSum(" [tblPerConcDetails]![ConcMass]","[tblPerConcDetails]","( [ConcNo] <= [tblPerConcDetails]![ConcNo] And [tblPerConcDetails]![GlobalID]=[GlobalID])") but no luck.

    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cumulative Totals in a Query (A2002 SP3)

    Try this expression - note the use of concatenation:

    Val(DSum("ProductMass","tblProductDetails","TestID = " & [TestID] & " And ProductIndex <= " & [ProductIndex]))

    (I don't understand your second expression, it uses different table and field names)

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cumulative Totals in a Query (A2002 SP3)

    er . . . what a fool - thanks Hans, I must have constructed dozens of clauses like that when generating SQL strings in code, but because I was in the query designer it confused my brain for some reason <img src=/S/blush.gif border=0 alt=blush width=15 height=15>.

    I was mistakenly asking Access to find WHERE "Field Name" = "Field Name" (as a string comparison) instead of "Field Name" = "Field Value" ! No wonder it included all records in the specified domain in the DSUM.

    By way of explanation, the second expression was copied directly from my query designer, but I mistakenly forgot to edit the field and table names as I had done in the first expression to make the problem easier to visualise.

Posting Permissions

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