1. 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. 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]

3. 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.

4. 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.

5. 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. Originally Posted by John Hutchison
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)

7. 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
•