I have to create a chart. I had no idea how to it. I tried in excel but can only create it as a pivot table.

It is a chart of the dates two events happen in a year.

Event A ---- Event B
20/11/06---- 27/03/07
10/10/06---- 27/06/07
11/10/06---- 27/06/07
1/11/06---- 11/01/07
8/09/07---- 8/10/07
14/12/06---- 14/12/06
27/07/08---- 1/10/07
21/01/07---- 11/01/07

So what I would like is a Chart with dates on the x-axis (by month) and the number of Events ie A or B on the y-axis. Of course the x-axis will be clustered (i have learnt that new word).

I am open to being able to create this type of table as a report in access and please really help cause my option 2 is not what i want to do.

Option 2

So if I am stuck with only creating this in a pivot table in excel. Is it really normal in access reporting to export my access data to an excel sheet in order to update a pivot table there.

Or even more complicated send the data to an excel sheet which updates a pivot table and that pivot table is embedded in an access form.

Ok so maybe my first post is aksing for spoonfeeding, which I do not expect.

Here is my further efforts to solve this myself.

from this

Event A ---- Event B
20/11/06---- 27/03/07
10/10/06---- 27/06/07
11/10/06---- 27/06/07
1/11/06---- 11/01/07
8/09/07---- 8/10/07
14/12/06---- 14/12/06
27/07/08---- 1/10/07
21/01/07---- 11/01/07

I will create two tables grouped by month


create table newtable as
(select month(tbl_yourtable.contactdate), year(tbl_yourtable.contactdate), count(*)
from tbl_yourtable
where tbl_yourtable.contactdate >= DateValue(Strstartdate)
And tbl_yourtable.contactdate <= DateValue(Strenddate)
group by month(tbl_yourtable.contactdate), year(tbl_yourtable.contactdate));

Result should be a table with

Month --- Contact
Jan ------ 2
Feb ------ 8
Mach -----5
and so on for each month

I do the same with another table for the second date

And then with these two tables I can very easily make a chart.

O am obviously open to comments or help.