Sorry about that - I posted without looking at the numbers.
Use two queries and a table named Count with numbers from zero to the
maximum number of months you will have. I used CR Dates as data table.
You
might add date criteria also.
Query named [Months from x-y]
SELECT Format(DateAdd("m",[count],[Date open]),"mm yyyy") AS [CR Dates]
FROM [Change Requests], [Count]
WHERE (((DateAdd("m",[count],[Date open]))<=[date close]))
ORDER BY Format(DateAdd("m",[count],[Date open]),"mm yyyy");
SELECT Format([CR Dates],"mmmm yyyy") AS [CR Months], Count(([CR Dates]))
AS
CRs
FROM [Months from x-y]
GROUP BY Format([CR Dates],"mmmm yyyy"), Format([CR Dates],"mm yyyy")
ORDER BY Format([CR Dates],"mm yyyy");
Ed said:
I have a table that contains open and closed dates for each record. I
want
to list the number of records that fit between these dates for each of
the
last 12 months. If a record is opened in Jan and closed in Apr, it should
show up for jan, feb, mar and apr.