Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2003
    Posts
    41

    Unanswered: Running Total by Period Ending Query

    Hey folks,

    I am trying to setup a running totals query by period ending, I have searched high and
    low and came up empty.
    The fields I have in the query are(the brackets indicate criteria):

    Field1("District1") - Field2 - Field3("B") - Period Ending - HoursWorked
    District1 - Test - B - 04/23/2010 - 25
    District1 - Test - B - 04/30/2010 - 15
    District1 - Test - B - 05/06/2010 - 10

    So I want to add another cumulative field that will total HoursWorked, so for the 1st record it would read 25, the 2nd will be 40, the third will be 50.
    I have tried the DSUM but I do not know how to factor in the criteria part. It appears like it sum's up all hours. Any suggestions would be great.

    Thanks,
    Last edited by justlearning200; 04-29-10 at 11:38. Reason: Subject update

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT  District1
          , Field2
          , B
          , [Period Ending]
          , HoursWorked
          ,     (
                    SELECT  SUM(HoursWorked) 
                    FROM    tabbyTabTab AS tabbyTabTaberson 
                    WHERE   tabbyTabTaberson.[period Ending] <=  tabbyTabTab.[period Ending]
                ) AS cumTot
    FROM    tabbyTabTab

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Note this sort of sub select (utilising a Theta join no less) is notoriously inefficient and the processing work load increases exponentially to the number of rows summed. If this is a large table and it runs poorly a recordset solution might be better {shudder}

  4. #4
    Join Date
    Jul 2003
    Posts
    41
    Thanks for your reply. I think I confused you with the query layout, below is the actual.

    SELECT tbl_ProjData.Client, tbl_ProjData.ProjectName, tbl_ProjData.[Project#], tbl_ProjData.Location, tbl_data.[Period Ending], tbl_data.Phase, tbl_Phase.Desc, tbl_data.Project, tbl_Project.Desc, tbl_data.SubProject, tbl_data.WBS, tbl_data.Disc, Sum(tbl_data.DirectHrs) AS SumOfDirectHrs
    FROM tbl_ProjData, tbl_CurPd, tbl_Project INNER JOIN (tbl_Contract INNER JOIN (tbl_Phase INNER JOIN tbl_data ON tbl_Phase.Phase = tbl_data.Phase) ON tbl_Contract.[Contract#] = tbl_data.[Contract#]) ON tbl_Project.Project = tbl_data.Project
    GROUP BY tbl_ProjData.Client, tbl_ProjData.ProjectName, tbl_ProjData.[Project#], tbl_ProjData.Location, tbl_data.[Period Ending], tbl_data.Phase, tbl_Phase.Desc, tbl_data.Project, tbl_Project.Desc, tbl_data.SubProject, tbl_data.WBS, tbl_data.Disc
    HAVING (((tbl_data.Phase)="Con") AND ((tbl_data.Project)="Mars") AND ((tbl_data.SubProject)="AreaB"));

    I am trying to put the running total on the DirectHrs field.
    This query is the basis of a report, do you recommend this be done at that level? or is it even possible?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ok - this is a common problem when abstracting a problem - the solution to the abstract may not be a solution to the problem.

    So you want a running sum of the sum?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT  tbl_ProjData.Client
          , tbl_ProjData.ProjectName
          , tbl_ProjData.[Project#]
          , tbl_ProjData.Location
          , tbl_data.[Period Ending]
          , tbl_data.Phase
          , tbl_Phase.desc
          , tbl_data.Project
          , tbl_Project.desc
          , tbl_data.SubProject
          , tbl_data.WBS
          , tbl_data.Disc
          , SUM(tbl_data.DirectHrs) AS SumOfDirectHrs
          ,     (
                    SELECT  SUM(HoursWorked) AS cumTot
                    FROM    tbl_data AS tbl_data_cum 
                    WHERE   tbl_data_cum.[period Ending]    <=  tbl_data.[period Ending]
                        AND tbl_data_cum.[Contract#]        =   tbl_data.[Contract#]
                        AND tbl_data_cum.Phase              =   tbl_Phase.Phase
                        AND tbl_data_cum.Project            =   tbl_Project.Project
                ) AS cumTot
    FROM    tbl_ProjData
          , tbl_CurPd
          , tbl_Project 
    INNER JOIN 
            (
                tbl_Contract 
            INNER JOIN 
                    (
                        tbl_Phase 
                    INNER JOIN 
                         tbl_data 
                    ON  tbl_Phase.Phase = tbl_data.Phase
                    ) 
            ON  tbl_Contract.[Contract#] = tbl_data.[Contract#]
            ) 
    ON tbl_Project.Project = tbl_data.Project
    WHERE   tbl_data.Phase="Con" 
        AND tbl_data.Project="Mars" 
        AND tbl_data.SubProject="AreaB"
    GROUP BY tbl_ProjData.Client
          , tbl_ProjData.ProjectName
          , tbl_ProjData.[Project#]
          , tbl_ProjData.Location
          , tbl_data.[Period Ending]
          , tbl_data.Phase
          , tbl_Phase.desc
          , tbl_data.Project
          , tbl_Project.desc
          , tbl_data.SubProject
          , tbl_data.WBS
          , tbl_data.Disc
    1) Your query looks dodgy - why are the first tow tables not joined to anything?
    2) I forgot to say - this is much more easily and efficiently done in a report rather than a query

  7. #7
    Join Date
    Jul 2003
    Posts
    41
    Hey,

    I would like to try the report route. I have attached some sample working data. Would this involve adding a single field in the report?

    Chris
    Attached Files Attached Files

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by justlearning200 View Post
    Would this involve adding a single field in the report?
    Yes, and just change the RunningSum property to true (IIRC).

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by justlearning200 View Post
    Would this involve adding a single field in the report?
    Yes, make the control source the sum of working hours column and change the RunningSum property to true (IIRC).

  10. #10
    Join Date
    Jul 2003
    Posts
    41
    Hey,

    The only options under Running Sum are No, Over Group, Over All.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    try the two ones that aren't "no" and use the one that works

  12. #12
    Join Date
    Jul 2003
    Posts
    41
    I should have mentioned that I tried both and they didn't work. In the attached report it is broken down:

    Period Ending
    SubProject
    WBS
    Field1, Field2, Hours, Field3

    So for example, period ending 10-Apr the cuml. total was 100. So from 11-Apr to the next period end, they worked another 50 hrs. My next cuml. total will be 150 for period ending 23-Apr. Because my report is broken down in this way, is it possible to have a field to cuml. the data.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've downloaded your db. Please can you repeat that last post, but refer to the data you uploaded so I don't have to work on interpreting stuff so hard

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or confirm this is what you want:
    130
    840
    1000
    1190

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Assuming I'm correct, stick a textbox in the group header for Period Ending (or create a footer for this group) and make it sum Over All on the SumOfTotalHrs column

Posting Permissions

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