Summary (Count) Of Multiple Date Fields By Month

[bigot]

Hi All,

What I have:
A table with multiple Date/Time columns. Here's an example:





What I would like:
A month-by-month summary counting the number of entries in each of the three columns, like this:



I'm indifferent as to what happens with the months zeros across all three fields (whether they show up in the report with zeros or are omitted altogether).




Smaller Pieces:
I am able to create such a summary for one column at a time:




Not what I'm looking for:
I can not figure out how to create a query that does this for all three columns. My best attempt was a disaster:








Any help/insight would be greatly appreciated. I have attached the example file for your convenience: Database1.accdb

Thank you.



EDIT:To clarify, my issue isn't the format of the date. I know about the Month(...) function. My question is regarding how to count this data and split it into a month-by-month summary (see the picture above in the "What I would like:" section)

[ItsMe]

The month function returns an integer.

untested
Dim intMonth as integer

intMonth= ("#1/1/2010#")

debug.print intMonth

[bigot]

The month function returns an integer.

I think you misunderstood the question. Whether using an integer or a year-month concatenated string is irrelevant. I don't know how to create a query that produces a result with the first column being the month (doesn't matter if int or string). The second column is the number of "BirthDate"s that occur in that month. The third column is the number of "OtherDate"s that occur in that month. and the Fourth column is the number of "DeathDate"s that occur in that month.

[ItsMe]

OK........

[bigot]

[Xipooo]

Here ya go.

qryBAD is the main query with the results you're looking for.

I made a union query that takes your qryBirth, qryOther, and qryDeath queries and captured all of the possible months in them. Then I did a left join of the Union query and each of the date queries based on the month values of each table. Then added an nz() function on the counts to make sure nulls resulted in 0. Voila.

[bigot]

Here ya go.

qryBAD is the main query with the results you're looking for.

I made a union query that takes your qryBirth, qryOther, and qryDeath queries and captured all of the possible months in them. Then I did a left join of the Union query and each of the date queries based on the month values of each table. Then added an nz() function on the counts to make sure nulls resulted in 0. Voila.

Perfect, this is exactly what I was looking to do!

I had tried joining, but I did not think to create a query to list all of the possible months (oops).


Thank you Xipooo.

If a reply resolves your issue, please click on the Thread Tools and mark it as Solved!
Also give some credit by Clicking on the Star in the lower left corner of their post!

Done and done.