Sum of open and closed dates

E

Ed

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.
 
K

KARL DEWEY

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");
 
E

Ed

The results are not good. The counts per month vary if I vary the [Count]
table.

SELECT Sum(IIf([dateopened]<=#3/1/2002# And [dateclosed]>=#2/1/2002#,1,0))
AS Expr1
FROM tblOPGMasterSpreadsheet AS T;

This is more what I want except that I want to vary the date.

ed

KARL DEWEY said:
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.
Click to expand...
Click to expand...
 
Top