Counting new, open and closed items by month

[mgrover2k2]

CR 9
MS Access 2000

I have a table with:
Item ID
Open Date
Closed Date

I'm trying to build a report grouped on month that summarizes:
-How many items were still open at the end of the month and what was the average age of the items.
-How many items were closed during the month and what was the average age when closed.

Since items could be open for several months, they need included in the summary for every month that they were open (through the end of).

It's the kind of thing that should be possible with a cross-tab, but I can't figure out how to indicate that it was open during a month that is not the start date or end date.

I was thinking about writing 12+ formulas to test which months/years the item was open during, but that seems wrong and I have no idea how I could graph that result.

If the explanation isn't enough, I'll try to post a mock-up of the table and report.

 

[Madawc]

I only know Cyrstal 8.5. If I were asked to write such a program, I'd do a 'mock-crosstab'. This is where you write a number of totals for the different months, which would count how many records were opened, how many were still opened and how many closed. It can be a bit tedious but it does work.

I don't think a true crosstab will work, because you want the same record to be in more than one total.

Madawc Williams (East Anglia)