Results 1 to 15 of 15
  1. #1
    KSDavis is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2017
    Posts
    7

    Running Totals: How to apply multiple criteria to DSum function


    I am trying to set up a query which includes running totals of my data so that I can make a cumulative graph in access. I have set up the formula: Production Standard - Running Total: DSum("[Production Standard]","M151-1A by Month (with standards)","Date <= #" & [Date] & "#") which is successful in creating a running total but I need it to only use data between a specific start and end date. My query is set to only display data between a start and end date but the running total includes all of the data. Is there a way to write in both criteria in this formula?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Where are the dates coming from? If a form then "Date Between Forms!formname!StartDate AND Forms!formname!EndDate"

    BTW, Date is a reserved word, you really should not use it as a field name.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Also advise no spaces or punctuation/special characters (underscore only exception) in name convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    KSDavis is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2017
    Posts
    7
    The query is set up so that the date criteria is set up as you suggested above. I need to be able to include this criteria in my DSum function as well. It need to add the Total conforming from the previous date as it is currently doing but also omit all data that is not between the specified start and end date. I have attached screen shots of the design view and the datasheet view for a visual description. The formula used for the running total for Production Standard is: Production Standard - Running Total: DSum("[Production Standard]","M151-3 by Month (with standards)","Date <= #" & [Date] & "#") and for Total conforming: Total Confrom - Running Total: DSum("[Total Conform]","M151-3 by Month (with standards)","Date <= #" & [Date] & "#"). The reason that the production standard running total is giving me the information I desire is that there is no data to that value prior to 9/1/17. I should also note that this a totals query created with data from another query.
    Attached Thumbnails Attached Thumbnails M151-3.png   M151-3 Query.png  

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I need to be able to include this criteria in my DSum function as well
    If this is your question, then my answer is - so why don't you?

    Functions within queries are extremely slow, it would be better to have a query with the values that you need.

  6. #6
    KSDavis is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2017
    Posts
    7
    I haven't included it because I can't figure out how to write it to successfully every way that I have tried has given me an error message. I was hoping that someone here could help me figure out how to include both criteria in my function. I do not mind that access with perform slowly as long as I can get the information that I require in order to set up these reports.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Domain Aggregate WHERE argument follows syntax of sql WHERE clause. Try [] around all object references.

    DSum("[Production Standard]", "[M151-3 by Month (with standards)]", "[Date] <=" & [Date] & " AND [Date] BETWEEN #" & someinput & "# AND #" & anotherinput & "#")

    Have you considered building report using its Sorting and Grouping features with aggregate calcs? Textbox on report has a RunningSum property.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    KSDavis is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2017
    Posts
    7
    I have tried the following function which selects the correct data but gives me a total sum rather than the running total.

    Production Standard - Running Total: DSum("[Production Standard]","[M151-3 by Month (with standards)]","[Date] Between #" & [StartDate] & "# And #" & [EndDate] & "#")

    I have not tried to run it in a report because I need the data to create a cumulative graph in a report. I am also fairly new to access (just started using it last week) so this is a learn as I go process.

    I also tried the following function with the same results.

    Total Conform- Running Total: DSum("[Total Conform]","[M274-1A by Month (with standards)]","[Date] Between #" & [Forms]![Form1]![StartDate] & "# And #" & [Forms]![Form1]![EndDate] & "#")

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Review my previous post again, perhaps you read it before I edited.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Copy the criteria from post # 4, without all the rest of the bits.

  11. #11
    KSDavis is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2017
    Posts
    7
    I feel like that is so much closer to what I am looking for thank you! I tried the following function without and error message but the field in the datasheet view is blank. Any idea of what might be wrong with it?

    Total Conform- Running Total: DSum("[Total Conform]","[M151-3 by Month (with standards)]","[Date] <=" & [Date] & " AND [Date] Between #" & [Forms]![Form1]![StartDate] & "# And #" & [Forms]![Form1]![EndDate] & "#")

    (Form1 is currently the name of the form that I'm using to input date values.)

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Since I don't know your db, no idea why that fails.

    Do you have an aversion to doing this in report design as already suggested?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    KSDavis is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2017
    Posts
    7
    I have tried running it in a report and it does give me the exact data that I'm looking for. I am trying to use the data to create a cumulative graph for the two totals by date and I'm unsure how to use data in a report to create it. I can create a pivot table but it does not use the running sim values just the original data. That is why I have been trying to get the function to work in a query. Is there a way to create a graph from data in a report that I am missing?

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No, the graph has its own dataset in RowSource property. Sorry, I lost track of the graph requirement mentioned in OP.

    Try including # delimiters: "[Date] <=#"& [Date] & "# AND

    I did some testing and this running sum should be possible with a nested subquery which might perform faster than domain aggregate. Review http://allenbrowne.com/subquery-01.html#YTD

    If still having issues, provide sample of your raw data. If you want to provide db, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    KSDavis is offline Novice
    Windows 10 Access 2003
    Join Date
    Sep 2017
    Posts
    7
    That worked perfectly!!!!!! I cannot thank you enough for your help. I have spend 3 solid days researching and trying to figure out a function that works and you just solved all of my issues in a few posts. For anyone who might need it in the future the final function looks like this:

    Production Standard- Running Total: DSum("[Production Standard]","[M274-1A by Month (with standards)]","[Date] <=#" & [Date] & "# AND [Date] Between #" & [Forms]![Form1]![StartDate] & "# And #" & [Forms]![Form1]![EndDate] & "#")

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

Similar Threads

  1. DSUM with multiple criteria
    By Bkper087 in forum Access
    Replies: 4
    Last Post: 02-13-2017, 10:19 PM
  2. Replies: 2
    Last Post: 09-09-2015, 09:45 PM
  3. Dsum function using mulitple criteria (specific: dates)
    By QA_Compliance_Advisor in forum Programming
    Replies: 10
    Last Post: 07-08-2015, 10:29 AM
  4. Use the Date as a criteria in Dsum function
    By Ank_db in forum Queries
    Replies: 1
    Last Post: 01-18-2015, 12:08 PM
  5. Replies: 43
    Last Post: 08-06-2014, 08:21 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