Results 1 to 9 of 9
  1. #1
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481

    Running Sum & Minus.

    Hi every one

    I need help to calculate running sum and minus between values as the DB is enclosed.

    a. The running sum should be of "Duration" from TablRHours1 based on each "RHID" key of TablRHours
    b. The difference should be between 1st value and 2nd value of "Temp" from TableRHours1 and divided by "Duration(hours)" in minutes and so on, to fill the data in the field "TempPerMin".

    Any earliest help would be appreciated.




    Regards

    Zee
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,360
    Bing: Access running sum
    Review https://support.microsoft.com/en-us/help/290136/how-to-create-a-running-totals-query-in-microsoft-access

    Be aware, query with domain aggregate function can perform very slowly with large dataset.
    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.

  3. #3
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Dear June

    Thanks for your feedback.

    Its hard to get the idea for my Demo DB. It would be appreciated, if you can help out by coding in given demo DB as per requirements.

    Thanks a lot.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,360
    SELECT TableRHours1.RHID, TableRHours.Descp, TableRHours1.Temp, Val(DSum("Temp","TableRHours1","RHID=" & [TableRHours1].[RHID] & " AND ID<=" & [ID])) AS TempPerMin
    FROM TableRHours INNER JOIN TableRHours1 ON TableRHours.RHID = TableRHours1.RHID;
    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.

  5. #5
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    Thanks for the efforts and it work fantastic grouped to RHID.
    but it was required for Duration not for temp so i changed as below.

    Code:
    RS: Val(DSum("Duration","TableRHours1","RHID=" & [TableRHours1].[RHID] & " AND ID<=" & [ID]))

    Can you help out for the question "b" as below
    The difference should be between 1st value and 2nd value of "Temp" from TableRHours1 and divided by "Duration(hours)" in minutes and so on, to fill the data in the field "TempPerMin".

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,360
    What are 1st value and 2nd value? Perhaps http://allenbrowne.com/subquery-01.html#AnotherRecord
    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.

  7. #7
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    The values given in the field of "Temp" where every next value must be minus from the previous one and divided by the "Duration" field to fill in the "TempPerMin" field.

    For example:

    Temp Duration
    (Hours)
    TempPerMin
    100 0.1 (100-0)/.10
    250 4.0 (250-100)/2.0
    400 6.0 (400-250)/6.0

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,360
    Per Allen's tutorial, consider:

    SELECT TableRHours.RHID, TableRHours.Descp, TableRHours.Notes, TableRHours1.ID, TableRHours1.Duration, TableRHours1.Temp, Val(DSum("Duration","TableRHours1","RHID=" & [TableRHours1].[RHID] & " AND ID<=" & [ID])) AS RH,
    ([Temp]-Nz((SELECT TOP 1 Dupe.Temp FROM TableRHours1 AS Dupe WHERE Dupe.RHID=TableRHours1.RHID AND Dupe.ID<TableRHours1.ID ORDER BY RHID, Dupe.ID DESC),0))/Duration AS TempPerMin
    FROM TableRHours INNER JOIN TableRHours1 ON TableRHours.RHID = TableRHours1.RHID;

    Again, nested query like this, as with domain aggregate functions, can perform very slowly with large dataset.


    I have been looking at the graph in your report. Build a grouping on the RHID field. Put a textbox named tbxRHID bound to RHID field in the group header section. Also put the chart in the group header section. Set chart RowSource to: SELECT [RH], [Temp] FROM [QueryRHours] WHERE [RHID] = [tbxRHID] ORDER BY [RH];
    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.

  9. #9
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hello June

    Thanks for help. its working good.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-04-2017, 07:34 PM
  2. DATE Minus 1 day
    By EileenAchil in forum Queries
    Replies: 2
    Last Post: 12-29-2016, 02:37 AM
  3. Plus and Minus buttons
    By usaemtp in forum Forms
    Replies: 13
    Last Post: 01-30-2016, 08:45 PM
  4. Sum IF else minus
    By AussieGal in forum Access
    Replies: 2
    Last Post: 03-07-2013, 07:15 PM
  5. Workaround for MINUS (with Where)
    By Autoclave in forum Queries
    Replies: 2
    Last Post: 10-31-2011, 08:15 AM

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