Results 1 to 15 of 15
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Similar to totals and Grand totals

    Although similar to the previous thread on totals, I am trying to get a more in-depth average

    SQL Query I have embedded the report:

    SELECT [Change Request].CR_No, [Change Request].[Change Requested], DateDiff("d",[Date_ID'd],[Date_Closed]) AS [Date ID_Closed]
    FROM [Change Request]
    WHERE ((([Change Request].[Change Requested])<>"Do not delete") AND (([Change Request].Action_Complete)=True) AND (([Change Request].Sub_No)=0));

    This is what I have made for the overall average (in Header/Footer) =Format(Sum([Date ID_Closed])/Count([CR_No]),"Standard")

    I am trying to get a rolling average in the details area for each row. CR........Change Requested......Date ID_Closed......Average




    I believe I have it. Still some more research to do: =Format([Date ID_Closed]/[CR_No],"Standard")

    If an easier way is found then LMK.

    Thanks


    I think I need to sum the output of the dates per row/CR_No to do this.

    Thanks

  2. #2
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I am using the following code in a control: =IIf([ID_Closed]=0,0,Format([CR_No]/Sum([ID_Closed]),"Standard"))

    I thought I had it solved, but I want it to sum up to the point of that row for the number of days ID closed equals.
    IE
    CR_No.....ID_Closed........Total
    1...............2..................2
    2...............3..................5
    3...............1..................6

    What am I missing? In the code above I think I have the sum of all the days at once.
    Last edited by June7; 01-23-2015 at 11:54 AM.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What is ID_Closed field - calculated based on what? I don't understand calc that divides elapsed time by CR_No. Isn't CR_No a record identifier?

    What do you mean by 'rolling' - year to date?

    If RunningSum property in textbox on report will not accomplish what you want, review:

    http://allenbrowne.com/subquery-01.html#YTD

    http://support.microsoft.com/kb/290136
    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
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    The elapsed time is the count of days it took to resolve the request. I want to average the number of days sequentially by row.

    CR_No....Date ID_Closed........Total Days.......Average (Total Days/CR_No)
    1...............2........................2........ .............2/1= 2
    2...............3........................5........ .............5/2= 2.5
    3...............1........................6........ .............6/3= 2

    This is what I have so far.
    SELECT [Change Request].CR_No, [Change Request].[Change Requested], DateDiff("d",[Date_ID],[Date_Closed]) AS ID_Closed, Sum([Change Request].[CR_No]) AS TTL
    FROM [Change Request]
    GROUP BY [Change Request].CR_No, [Change Request].[Change Requested], DateDiff("d",[Date_ID],[Date_Closed]), [Change Request].Action_Complete, [Change Request].Sub_No
    HAVING ((([Change Request].[Change Requested])<>"Do not delete") AND (([Change Request].Action_Complete)=True) AND (([Change Request].Sub_No)=0));

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How is CR_No generated? Guarantee no gaps in sequence?

    Running sum is not something I have to work with. Did the references help?
    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.

  6. #6
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    There would be no gaps in the sequence. There is a where which the Sub_CR must = 0 so that it does not count for all the subsets of each CR_No if there are any.

    I looked at them quickly, but couldn't figure out the meaning. I think it is implying this must be done in VB instead of an unbound control in the report?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The Allen Browne reference is a query solution for a year-to-date calc.
    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
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I'll take anothergander, but it seems an excessive way.

    The dates I am calculating are in the same row. Basically the start date and the closed date.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Oh, then I really don't understand the data and the issue. If you just want to calc DateDiff and divide by CR_No on each record, that is simple enough and your query should be correct.
    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
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Almost. I want the accumulation of the days totaled of each record divided by the CR_No.
    Exapmple:

    CR1 has 3 days, CR2 has 3 Days, CR 3 has 5 Days

    CR1 is 3 days/1 = 3 average
    CR2 is (3 days + CR1 3days)=6 days/2 = 3 average
    CR3 is (5 days + CR1 3 days + CR 2 2 days) = 11 days/3 =3.6 average

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want to aggregate data for a running sum, then the options are:

    1. report textbox RunningSum property

    2. nested subquery

    3. domain aggregate DSum

    In that order of preference.
    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.

  12. #12
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I found the way to make a running sum. Under property sheet, select the control, then go to the data tab and change Running sum to "Over All" The part I am trying to figure out is how do I take that running sum and divide it by the CR number.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    In the same textbox:

    = [Total Days] / [CR_No]

    Or in another textbox reference the running sum textbox.
    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.

  14. #14
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    =Format([TTL]/[CR_No],"#") in textbox
    In the TTL textbox I put the "Over All" in the data tab.

    SELECT Query1.CR_No, Query1.[Change Requested], Query1.Days_Open, ([Days_Open]) AS TTL
    FROM Query1
    GROUP BY Query1.CR_No, Query1.[Change Requested], Query1.Days_Open, ([Days_Open]);

    Worked out great.

    I found where my issue was. I had some code on the textbox in VBA that I didn't delete.

  15. #15
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks June7

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

Similar Threads

  1. Totals and Grand Totals
    By TimC in forum Reports
    Replies: 2
    Last Post: 01-05-2015, 11:56 AM
  2. Replies: 3
    Last Post: 02-14-2014, 10:51 AM
  3. Unusual Sub Totals & Totals in Groups.
    By Robeen in forum Reports
    Replies: 12
    Last Post: 03-20-2012, 08:55 AM
  4. Replies: 5
    Last Post: 12-06-2011, 11:18 AM
  5. Month totals and Year totals
    By marksnwv in forum Access
    Replies: 1
    Last Post: 08-05-2011, 10:13 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