Results 1 to 6 of 6
  1. #1
    DMcTegra is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    3

    Dsum w/Multiple Criteria

    Hello! I'm struggling with setting up a dsum expression that captures the information I want, calculating a running sum that resets based on a category number.



    I'm working on a goal setting tool in a production environment. I have a goal time calculated for each job scheduled to a machine. I'm trying to capture a running sum so I can evaluate at what point the total goal time exceeds the time in a shift for each machine.

    As you can see below, I've been successful getting a dsum set up that will work for all of the jobs (sequence#), but I need it to reset to 0 when it moves from press N02 to N03 and so on. The expression used to create the below is:

    Rs: DSum("[Goaltime]","[tblPressGoals_Efficiency]","[Sequence#]<=" & [Sequence#])

    When I try adding "Press" into the criteria, it either gives me an error, repeats the same total for every record, or comes up blank.. Hoping someone here can help me out?
    Press Sequence# GoalTime Rs
    N02 14907 52.528 52.528
    N02 14909 266.728 319.256
    N03 14916 69.23 388.486
    N03 14917 76.58 465.066
    N03 14918 98.84 563.906
    N03 14919 98.84 662.746
    N03 14920 100.31 763.056
    N03 14921 98.84 861.896
    N03 14922 98.84 960.736
    N03 14923 89.32 1050.056
    N03 14924 98.84 1148.896

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Is this output in a query, form or report?

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You have to at least show what you have tried

    I tend to put all the criteria into a string variable, debug.print that and when correct, use that in the function.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    DMcTegra is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    3
    Quote Originally Posted by Bulzie View Post
    Is this output in a query, form or report?
    Trying to output in a query.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Possibly
    Code:
    Rs: DSum("[Goaltime]","[tblPressGoals_Efficiency]","[Sequence#]<=" & [Sequence#] & " AND {Press] = '" & [Press] & "'")
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    DMcTegra is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    3
    Sorry for the delay. This worked perfectly!!!!!!!!!!! My hero!!!!

    Quote Originally Posted by Welshgasman View Post
    Possibly
    Code:
    Rs: DSum("[Goaltime]","[tblPressGoals_Efficiency]","[Sequence#]<=" & [Sequence#] & " AND {Press] = '" & [Press] & "'")

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DSUM Multiple Criteria
    By justdone in forum Queries
    Replies: 3
    Last Post: 10-08-2020, 07:41 PM
  2. Help! Stuck on DSUM with multiple criteria...
    By tbraswell in forum Access
    Replies: 2
    Last Post: 01-03-2019, 10:13 AM
  3. DSUM with multiple criteria
    By Bkper087 in forum Access
    Replies: 4
    Last Post: 02-13-2017, 10:19 PM
  4. DSum with multiple criteria not resetting
    By ms13103 in forum Queries
    Replies: 3
    Last Post: 02-04-2016, 09:34 AM
  5. How to use a create a DSum with multiple criteria
    By FlyingDisc in forum Reports
    Replies: 1
    Last Post: 01-05-2011, 08:31 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums