Chart for annual comparison of data by months

[shindb81]

Hi.
I have a small clinic database.
I've got tblAppointments to show AppointmentID and AppointmentDate among various other data, but only the dates matter for what I'm trying to achieve.

I'm trying to show a chart on a report that shows the number of appointments by months for this year and previous year. How can I do this...without using SQL, hopefully?

Here's what I tried:


I made a cross-tab query to successfully show the years 2013 and 2014 in the rows, months in the columns, and number of appointments as values. But then, I didn't know how to graph it to compare the number of appointments for the two years by months.

Thanks.

[June7]

Try setting that query as the RowSource of a graph object.

I did a quick test for a bar chart and the years became the x-axis, the months the series, and the count was the y-axis.

[shindb81]

Thanks for looking into this.
Yes, what you've said will produce a kind of graph that is accurate to description. But I was hoping to compare each month of the two years side to side.
The attached is the graph I want, which I was able to create with a click of a button in Excel.
If exporting the query data automatically to excel and also graphing it automatically is possible with some VBA code, I'd like to know how to do that.

[June7]

Then change the CROSSTAB so the months are the row headers and years are the column headers.

The months become the x-axis and the years are the series, count is still y-axis.

I get exactly what you posted.

[shindb81]

Wow, problem solved!
Thanks a bunch.