Cumulative counts in a query

[Boss]

Hi,

I have a master table (Two column) with ProjectID as primary key and
received date as second column.

ProjectID Received date
1 10-07-06
2 12-11-07
3 01-03-08
4 11-05-08
5 10-07-09


I need to find out the number of project started and the cumulative count on
each month in the past years.

How should i go about this... pls help...


SELECT Year(Master![Received Date]) AS Expr1, Format(Master![Received
Date],"mmmm") AS Expr2, [Expr1] & " " & [Expr2] AS [Month-Year],
Master.[ProjectID], Month(Master![Received Date]) AS Expr3
FROM Master;

After this query i again run a new query to get count of project for each
month. I know there is a better way to do this...

Guys also help for getting the cumulative count of projects.

I have about 500 rows in table. Thx!

Thanks!
Boss

 

[Allen Browne]

So you want a record for each month, showing you the count of projects
started by that date.

1. You will need a way to generate a record for every month. Create a table
with just one field like this:
CountID Number
Mark the field as primary key, and save the table with a name such as
tblCount. Enter records starting from 0, then 1, and so on to the maximum
number of months you will need to deal with.

2. Create a query using tblCount.
Type this expression into the Field row:
TheMonth: DateAdd("m", [CountID], #1/1/2000#)
Change the 2000 to the earliest year you want to report on. The query will
give you a record for the first of each month.

3. Depress the Total button on the Toolbar.
Access adds a Total row to the query design grid.
Accept Group By under your TheMonth field.

4. Add your master table to this query.
Add the ProjectID field to the grid.
In the Total row under this field, choose Count.

5. Add the [Received date] field to the query grid.
In the Total row under this choose Where.
In the Criteria under this, enter:
<= DateAdd("m", [CountID], #1/1/2000#)

The query gives you a record for the first of each month, and the number of
projects that had been started before that month began.

 

[Boss]

Thanks alot for your hlep... for me it would take a bit to do this as i am
not so good at access.

would surely try out and reply back...

Thx!

So you want a record for each month, showing you the count of projects
started by that date.

1. You will need a way to generate a record for every month. Create a table
with just one field like this:
CountID Number
Mark the field as primary key, and save the table with a name such as
tblCount. Enter records starting from 0, then 1, and so on to the maximum
number of months you will need to deal with.

2. Create a query using tblCount.
Type this expression into the Field row:
TheMonth: DateAdd("m", [CountID], #1/1/2000#)
Change the 2000 to the earliest year you want to report on. The query will
give you a record for the first of each month.

3. Depress the Total button on the Toolbar.
Access adds a Total row to the query design grid.
Accept Group By under your TheMonth field.

4. Add your master table to this query.
Add the ProjectID field to the grid.
In the Total row under this field, choose Count.

5. Add the [Received date] field to the query grid.
In the Total row under this choose Where.
In the Criteria under this, enter:
<= DateAdd("m", [CountID], #1/1/2000#)

The query gives you a record for the first of each month, and the number of
projects that had been started before that month began.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a master table (Two column) with ProjectID as primary key and
received date as second column.

ProjectID Received date
1 10-07-06
2 12-11-07
3 01-03-08
4 11-05-08
5 10-07-09


I need to find out the number of project started and the cumulative count
on
each month in the past years.

How should i go about this... pls help...


SELECT Year(Master![Received Date]) AS Expr1, Format(Master![Received
Date],"mmmm") AS Expr2, [Expr1] & " " & [Expr2] AS [Month-Year],
Master.[ProjectID], Month(Master![Received Date]) AS Expr3
FROM Master;

After this query i again run a new query to get count of project for each
month. I know there is a better way to do this...

Guys also help for getting the cumulative count of projects.

I have about 500 rows in table. Thx!

Thanks!
Boss