How to create a chart comparing annual data counts?

[sueb]

I have a database that stores information about surgeries performed at the hospital where I work. One of the pieces of data is the date of each surgery. I need to make a chart that counts these surgeries by month, and compares these monthly counts across years in a clustered column chart where the clusters are the months, and the columns are the years.

I know I could export the data to Excel and shove it around mightily (this very common business need doesn't seem to be simply provided), but I was hoping that Access would be a little smarter.

Any help with this?

[NeoPa]

You can create a query where two of the output fields are Month([YourDate]) and Year([YourDate]).

With this set up (either as a QueryDef or a simple SQL string) you can feed this as a sub-query (See Subqueries in SQL) into a CrossTab query (Using the TRANSFORM clause).

[sueb]

Wow, almost everything in your reply is new to me in Access, so it will take some time before I can figure out how to do this. Thanks, though, for pointing me in the right direction!

[NeoPa]

My pleasure Sue :-)