Results 1 to 9 of 9

Thread: Quarterly data

  1. #1
    Join Date
    Sep 2003
    Posts
    67

    Unanswered: Quarterly data

    I need to produce a report that shows training hours planned vs. actual on a quarterly basis.

    I created a simple query [PlanHours], [ActualHours], [PlanDate], and [ActualDate], then created a report that groups and sums the data by quarter based on [PlanDate].

    However, there are several cases where training was taken outside the quarter in which it was scheduled. For example, training planned for 4th quarter was actually completed 3rd quarter, so the hours show up in the 4th quarter sum, even though they should be included in the 3rd quarter.

    The query I have is clearly wrong for my purpose. What do I need in order to show the [ActualHours] in their correct quarter but still group the report based on [PlanDate], or is this even the right question?

    Thanks for any assistance!!

    Jaycee

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    The simple answer is to create a simple query to correct the data. Use this to feed the sum query.

    If this is not enough help, post the sql text of your query.

  3. #3
    Join Date
    Sep 2003
    Posts
    67
    Thanks for your response. I'm new at SQL, so if I need to do something complex, please talk slowly and use small words!

    _____________________________________

    SELECT TrainingData.[Last Name], TrainingData.[Plan Hours], TrainingData.[Actual Hours], TrainingData.[Plan Date], TrainingData.[Actual Date]

    FROM TrainingData

    GROUP BY TrainingData.[Last Name], TrainingData.[Plan Hours], TrainingData.[Actual Hours], TrainingData.[Plan Date], TrainingData.[Actual Date]

    ORDER BY TrainingData.[Last Name];

  4. #4
    Join Date
    Sep 2003
    Location
    Gloucester
    Posts
    28
    Have you tried putting a second level of grouping in your report?

    ie - use the report wizard to get a feel for it

    When the wizard asks you how you want to group your data

    Select the first level of grouping - Planned date

    Then - select a second Level of Grouping - Actual Date

    On the summary button press

    Summary and detail for both levels


    Dont look too pretty - but it kinda does what you want

  5. #5
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    this query will give you a total of the hours based on the planned date:

    SELECT TrainingData.[Last Name], sum(TrainingData.[Plan Hours]) as SPH, sum(TrainingData.[Actual Hours]) as SAH, TrainingData.[Plan Date]

    FROM TrainingData

    GROUP BY TrainingData.[Last Name], um(TrainingData.[Plan Hours]), sum(TrainingData.[Actual Hours]), TrainingData.[Plan Date]

    ORDER BY TrainingData.[Last Name];

    If you need the actual hours to be in their own quarter only when they occurred after the planned quarter, you will have to use an IIf statement to get the entire dataset as you wish it to show first.

  6. #6
    Join Date
    Sep 2003
    Posts
    67

    Close, but no cigar

    This looked like it was going to be workable, but it's still not putting the ActualDate in its correct quarter. For example, a class planned for December was completed in August, but the actual hours are in the 4th quarter total.

    I'm trying to get all planned hours in their correct quarter, and all actual hours in their correct quarter -- the problem is that the planned and actual dates (and hours) are in the same record. Is there a way to show planned in one quarter and actual in another (where they're different) from the same record?


    Originally posted by Jumper
    Have you tried putting a second level of grouping in your report?

    ie - use the report wizard to get a feel for it

    When the wizard asks you how you want to group your data

    Select the first level of grouping - Planned date

    Then - select a second Level of Grouping - Actual Date

    On the summary button press

    Summary and detail for both levels


    Dont look too pretty - but it kinda does what you want

  7. #7
    Join Date
    Sep 2003
    Posts
    67

    Error message

    Thanks for the detail! I copied/pasted; the only change I made was to add a missing "s".

    I'm getting an error message, though: "Cannot have aggregate function in GROUP BY clause (sum(TrainingData.[Plan Hours])." What needs to be done to resolve the error? I'm hopeful that this will work! (Also, see my response to "Jumper" in case that's helpful at all.) Thanks again!

    Jaycee


    Originally posted by jmrSudbury
    this query will give you a total of the hours based on the planned date:

    SELECT TrainingData.[Last Name], sum(TrainingData.[Plan Hours]) as SPH, sum(TrainingData.[Actual Hours]) as SAH, TrainingData.[Plan Date]

    FROM TrainingData

    GROUP BY TrainingData.[Last Name], um(TrainingData.[Plan Hours]), sum(TrainingData.[Actual Hours]), TrainingData.[Plan Date]

    ORDER BY TrainingData.[Last Name];

    If you need the actual hours to be in their own quarter only when they occurred after the planned quarter, you will have to use an IIf statement to get the entire dataset as you wish it to show first.

  8. #8
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    SELECT TrainingData.LastName, Sum(TrainingData.PlanHours) AS SPH, Sum(TrainingData.ActualHours) AS SAH, TrainingData.PlanDate
    FROM TrainingData
    GROUP BY TrainingData.LastName, TrainingData.PlanDate
    ORDER BY TrainingData.LastName;

    you will need to put your [] and spaces back in though

  9. #9
    Join Date
    Sep 2003
    Posts
    67
    OK, the query works. (Yay!)

    Now if I can get my report to display the data in a useful way (total planned and actual for each quarter, then YTD for the report), I'll be set. I think I can at least get close, but stay tuned ... I may be back!

    Thanks very much for your help!!!!


    Originally posted by jmrSudbury
    SELECT TrainingData.LastName, Sum(TrainingData.PlanHours) AS SPH, Sum(TrainingData.ActualHours) AS SAH, TrainingData.PlanDate
    FROM TrainingData
    GROUP BY TrainingData.LastName, TrainingData.PlanDate
    ORDER BY TrainingData.LastName;

    you will need to put your [] and spaces back in though

Posting Permissions

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