Open and close query

[Ed]

I have a table that lists "Change Requests" (CR). The table has fields for
'date opened' and 'date closed'. I want to have a query that will show how
many CRs are currntly open in a month for a year. That is, if a CR is opened
in 1/1/2005 and closed in 3/1/2005 it would show up in the count for Jan,
Feb, and March, but not in April.

Likewise, if a CR is opened in 2/1/2005 and closed in 4/1/2005 it would show
up in the count for Feb, Mar, and Apr. The query results would look like:

Jan Feb Mar Apr May
1 2 2 1 0

 

[Guest]

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], Sum(1) AS CRs
FROM [Change Requests], [Months from x-y]
WHERE ((([Months from x-y].[CR Dates]) Between Format([Date open],"mm yyyy")
And Format([Date close],"mm yyyy")))
GROUP BY Format([CR Dates],"mm yyyy"), Format([CR Dates],"mmmm yyyy");

 

[Ed]

I had trouble implementing your solution. It took a long time for the query
to run and the sums seemed way off.
I created a table with one field of integars from 1 to 12.

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], Sum(1) AS CRs
FROM [Change Requests], [Months from x-y]
WHERE ((([Months from x-y].[CR Dates]) Between Format([Date open],"mm
yyyy")
And Format([Date close],"mm yyyy")))
GROUP BY Format([CR Dates],"mm yyyy"), Format([CR Dates],"mmmm yyyy");

I have a table that lists "Change Requests" (CR). The table has fields
for
'date opened' and 'date closed'. I want to have a query that will show
how
many CRs are currntly open in a month for a year. That is, if a CR is
opened
in 1/1/2005 and closed in 3/1/2005 it would show up in the count for Jan,
Feb, and March, but not in April.

Likewise, if a CR is opened in 2/1/2005 and closed in 4/1/2005 it would
show
up in the count for Feb, Mar, and Apr. The query results would look like:

Jan Feb Mar Apr May
1 2 2 1 0

 

[Guest]

Sorry about that - I posted without looking at the numbers. This should do
it for the second query.

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

I had trouble implementing your solution. It took a long time for the query
to run and the sums seemed way off.
I created a table with one field of integars from 1 to 12.

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], Sum(1) AS CRs
FROM [Change Requests], [Months from x-y]
WHERE ((([Months from x-y].[CR Dates]) Between Format([Date open],"mm
yyyy")
And Format([Date close],"mm yyyy")))
GROUP BY Format([CR Dates],"mm yyyy"), Format([CR Dates],"mmmm yyyy");

I have a table that lists "Change Requests" (CR). The table has fields
for
'date opened' and 'date closed'. I want to have a query that will show
how
many CRs are currntly open in a month for a year. That is, if a CR is
opened
in 1/1/2005 and closed in 3/1/2005 it would show up in the count for Jan,
Feb, and March, but not in April.

Likewise, if a CR is opened in 2/1/2005 and closed in 4/1/2005 it would
show
up in the count for Feb, Mar, and Apr. The query results would look like:

Jan Feb Mar Apr May
1 2 2 1 0

 

[Ed]

Thanks KARL

Very fast and the numbers look reasonable.

ed

Sorry about that - I posted without looking at the numbers. This should
do
it for the second query.

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

I had trouble implementing your solution. It took a long time for the
query
to run and the sums seemed way off.
I created a table with one field of integars from 1 to 12.

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], Sum(1) AS CRs
FROM [Change Requests], [Months from x-y]
WHERE ((([Months from x-y].[CR Dates]) Between Format([Date open],"mm
yyyy")
And Format([Date close],"mm yyyy")))
GROUP BY Format([CR Dates],"mm yyyy"), Format([CR Dates],"mmmm yyyy");


:

I have a table that lists "Change Requests" (CR). The table has fields
for
'date opened' and 'date closed'. I want to have a query that will show
how
many CRs are currntly open in a month for a year. That is, if a CR is
opened
in 1/1/2005 and closed in 3/1/2005 it would show up in the count for
Jan,
Feb, and March, but not in April.

Likewise, if a CR is opened in 2/1/2005 and closed in 4/1/2005 it
would
show
up in the count for Feb, Mar, and Apr. The query results would look
like:

Jan Feb Mar Apr May
1 2 2 1 0