Results 1 to 7 of 7

Thread: sum by month

  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Access 2003 (2000 format)

    I have a tblCancelledMeetings with the following makeup:
    MeetingCancelledID ... AutoNumber
    DateCancelled ... Date/Time
    MeetingCancelled ... Yes/No

    What I want to do is get a Sum by Month of the meetings cancelled during each month. The great majority of months will not have any cancelled meetings.

    Thanks.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You need a Group By Query, Grouping On Month and Using a Conditional SUM to Count where they are Cancelled ONLY
    You might also need to add the Year Component IF your data rolls over a Year


    The Calculated Field for the Month Could be

    Code:
    Year: Year([DateCancelled])                           -- Group By
    Month: Month([DateCancelled])                       -- Group By
    Cancelled: IIf([MeetingCancelled]=True,1,0)      -- Sum

    [attachment=87631:CancelledMeetingQuery.jpg]


    Or you could replace Month and Year with

    [font="'Courier New"]Month: Format([DateCancelled],"yyyymm") [/font]

    If You Want The Spelling Of the Month Add an Extra Group By After Month

    [font="'Courier New"]MonthName: Format([DateCancelled],"mmm yy")[/font]
    Attached Images Attached Images
    Andrew

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Why do yo need both DataCancelled and the yes/No field in the table?
    If there is a value in DateCancelled does not that mean that the meeting was cancelled?

    When would you you have a record with a DateCancelled, and False as the value for the yes/No field?

    You can also find the Count of cancelled meetings by adding the autonumber field to the query, and using a Count on the Total line.
    Regards
    John



  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Code:
    Cancelled: IIf([MeetingCancelled]=True,1,0)
    This is unnecessarily complicated.

    Yes values are stored as -1, False values as 0. So if you just sum the lot, the False values don't make any difference to the total.

    So -1* Sum([MeetingCancelled]) will give the same number.
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yep, the Yes/No field can go. The following SQL works...

    Code:
    SELECT CDate(Format([DateCancelled],"MMM yyyy")) AS MonthYear, Count(tblCancelledMeetings.MeetingCancelledID) AS CountOfMeetingCancelledID
    FROM tblCancelledMeetings
    GROUP BY CDate(Format([DateCancelled],"MMM yyyy"));
    Tom

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by John Hutchison View Post
    Code:
    Cancelled: IIf([MeetingCancelled]=True,1,0)
    This is unnecessarily complicated.

    Yes values are stored as -1, False values as 0. So if you just sum the lot, the False values don't make any difference to the total.

    So -1* Sum([MeetingCancelled]) will give the same number.
    That is quite true in this case John, but as a general principle for conditional counting
    it can sometimes be a useful method if the field is NOT boolean.

    i.e. LowNos: IIf([CancelReason]="Low Numbers",1,0)

    Andrew

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    By the way, this SQL works
    Code:
    SELECT CDate(Format([dateCancelled],"MMM yyyy")) AS monthYear, Sum(-1*[meetingCancelled]) AS [Total Cancelled]
    FROM tblCancelledMeetings
    GROUP BY CDate(Format([dateCancelled],"MMM yyyy"))
    ORDER BY CDate(Format([dateCancelled],"MMM yyyy"));
    as does this
    Code:
    SELECT CDate(Format([DateCancelled],"MMM yyyy")) AS MonthYear, Count(tblCancelledMeetings.MeetingCancelledID) AS CountOfMeetingCancelledID
    FROM tblCancelledMeetings
    GROUP BY CDate(Format([DateCancelled],"MMM yyyy"));
    Tom

Posting Permissions

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