Counting number of occurances by date

[michaelwh1]

I have a table that tracks differents events and the acctual date they occured. I am trying to write a query that counts the number of each event that occured by month. One issue I am having is if an event had no occurence during a month I want to return 0 raher than no data for that event. Help is appreciated.

[June7]

Options:

1. Need a dataset of all possible month and event combinations. This dataset could be created by a cartesian join of a months table and events table (or queries that generate those sets). Review attached file. In this example, I have one table that has at least one record for every possible event (only two events) and a record for every possble month. Each month has one or the other or both events. If that would not be the case for you then have a table of all events that could be scheduled. Also, if cannot rely on the table having at least one record for each month, then need a table of months.

2. Use DCount to query the table. Would need a calc for each month/event pair. How many events do you have? Example:
DCount("*","tablename","Month([EventDate])=x AND EventID=x")

[michaelwh1]

Thanks. This solution works. Very much appreciated.