Count(*) with different period

[=?iso-8859-1?B?VHNjaHXf?=]

Hi All,

The query I try to make is really difficult for me. I try to don't
make it with a personnal function but I'm not sure that is possible.

I have a Table with the following data (nb: format date dd/mm/yyyy)

Contract Number Start End
00001 01/01/2005 02/05/2008
00002 01/03/2004 21/08/2007
00003 01/03/2003 21/10/2007
00004 07/05/2003 21/11/2007
00005 01/12/2007 21/10/2010

I want to count how many contracts are activ for each month of 2007

So the result should be

Month Count
July 4
August 4
September 3 (contract 00002 is finished)
October 3
November 2 (contract 00003 is finished)
December 2 (contract 00004 is finished and 00005 is now activ)

Is there a query able to calculate this ?

 

[Guest]

You say that contract 00003 and 00004 are finished, yet the end date is in
the future. How do you tell if a contract is finished or not? Answer that and
the query is quite easy.

 

[=?iso-8859-1?B?VHNjaHXf?=]

lol

I didn't see the mistake.
Of course you're right, in the future, it is not possible. Well, the
max date is today of course. I didn't choose the best date for my
example ; I should choose August and September.

Can you do that ????

 

[Michel Walsh]

Have a table with one row per month:


DesiredMonths ' table name
FirstOfMonth 'field name
2007.06.01
2007.07.01
2007.08.01
2007.09.01
2007.10.01
2007.11.01
2007.12.01 ' data sample


then the query


SELECT FirstOfMonth, COUNT(*) As numberOfActiveContracts
FROM DesiredMonths AS d INNER JOIN yourTable As c
ON c.start >= d.firstOfMonth AND c.end<d.firstOfMonth
GROUP BY FirstOfMonth



should do. It assumed that a contract ends at 00:00:00, not at 23:59:59 of
the mentioned date under End.


Hoping it may help,
Vanderghast, Access MVP

 

[=?iso-8859-1?B?VHNjaHXf?=]

Let's try

Have a table with one row per month:

DesiredMonths ' table name
FirstOfMonth 'field name
2007.06.01
2007.07.01
2007.08.01
2007.09.01
2007.10.01
2007.11.01
2007.12.01 ' data sample

then the query

SELECT FirstOfMonth, COUNT(*) As numberOfActiveContracts
FROM DesiredMonths AS d INNER JOIN yourTable As c
ON c.start >= d.firstOfMonth AND c.end<d.firstOfMonth
GROUP BY FirstOfMonth

should do. It assumed that a contract ends at 00:00:00, not at 23:59:59 of
the mentioned date under End.

Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Whoops! I spoke too soon about the query being easy. As I reread your
requirements, it looks like you need a running sum. I'll have to think about
that a little more; however, I'm a little swamped right now at work.