Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    17

    Unanswered: Grouping and Summing by Month in a Report without Listing by Date

    I am trying to create a report that shows quantity produced by various departments over the course of the year.

    Each department produces a quantity on a daily basis. I would like to sum this quantity to create a monthly total. I would like to display the data like this:

    January
    ---Dept 1 - qty for January
    ---Dept 2 - qty for January
    ---Dept 3 - qty for January

    February
    ---Dept 1 - qty for February
    ---Dept 2 - qty for February
    ---Dept 3 - qty for February

    and so on.
    I have tried summing qty by month in the query. However, when I do this, the report treats the month field like text, not date, and sorts them alphabetically.

    I have tried grouping in the report by month, but then I get individual entries for every day of the month instead of a total for each department.

    Is there some way to group by month and also sum by month?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by GLink View Post
    I have tried summing qty by month in the query. However, when I do this, the report treats the month field like text, not date, and sorts them alphabetically.
    could you please copy the sql code here please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    17
    This is the summed by month that gets treated like text.

    SELECT DISTINCTROW [Production Log].Department, Format$([Production Log].[Order Date],'mmmm yyyy') AS [Order Date By Month], Round(Sum([Weight]/2000),2) AS [Sum Of Weight], [Production Log].Reason, [Production Log].[Order Date]
    FROM [Production Log]
    GROUP BY [Production Log].Department, Format$([Production Log].[Order Date],'mmmm yyyy'), [Production Log].Reason, [Production Log].[Order Date], Year([Production Log].[Order Date])*12+DatePart('m',[Production Log].[Order Date])-1
    HAVING (((Format$([Production Log].[Order Date],'mmmm yyyy')) Is Not Null));

    Thanks!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try
    Code:
    GROUP BY [Production Log].Department, Format$([Production Log].[Order Date],'yyyy mm')
    that will group by year and month. yyy returns a 4 digit year, mm returns a two digit month

    I think you can simplify the having clause to
    Code:
    having not isnull([Production Log].[Order Date])
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem View Post
    I think you can simplify the having clause to
    Code:
    having not isnull([Production Log].[Order Date])
    i think you can simplify the HAVING clause down to nothing, by moving that condition to the WHERE clause where it belongs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2011
    Posts
    17
    Thanks! That did the trick. It still doesn't recognize them as dates, but at least the order is right.

Posting Permissions

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