Counting Table (By Date)

Is there a way to make an table/query in Access that counts by date?
For example, if I have multiple projects with different start dates,
I'd like to have a running count of the months for the project... e.g.

Start Date 02/28/2010 03/1/2010 05/31/2010
Project A B
C

Date
1/2010 0 0 0
2/2010 1 0 0
3/2010 2 1 0
4/2010 3 2 0
5/2010 4 3 1


Etc. I'm attempting to build this in design mode, but can't think of
an efficient way to do it.

You did not show how you are storing the data but it needs to be like this --
AutoNUM Project Start_Date
1 A 3/5/2009
2 B 1/22/2010
3 C 11/2/2008
4 D 9/15/2009

Then your query in SQL view would be like this --
SELECT Format([Proj_Data].[Start_Date], "yyyymm") AS ProjYRMon,
Format([Proj_Data].[Start_Date], "mmm yyyy") AS Proj_Month, (SELECT
Count([XX].[Project]) FROM [Proj_Data] AS [XX] WHERE
Format([XX].[Start_Date], "yyyymm") = Format([Proj_Data].[Start_Date],
"yyyymm")) AS CountOfProject
FROM [Proj_Data]
WHERE [Proj_Data].[End_Date] Is Null
GROUP BY Format([Proj_Data].[Start_Date], "yyyymm")
ORDER BY Format([Proj_Data].[Start_Date], "yyyymm");

--
Build a little, test a little.


"Mike" wrote:

Is there a way to make an table/query in Access that counts by date?
For example, if I have multiple projects with different start dates,
I'd like to have a running count of the months for the project... e.g.

Start Date 02/28/2010 03/1/2010 05/31/2010
Project A B
C

Date
1/2010 0 0 0
2/2010 1 0 0
3/2010 2 1 0
4/2010 3 2 0
5/2010 4 3 1


Etc. I'm attempting to build this in design mode, but can't think of
an efficient way to do it.
.

I forgot to add that to get the display you want then use a crosstab query.
--
Build a little, test a little.


"KARL DEWEY" wrote:

You did not show how you are storing the data but it needs to be like this --
AutoNUM Project Start_Date
1 A 3/5/2009
2 B 1/22/2010
3 C 11/2/2008
4 D 9/15/2009

Then your query in SQL view would be like this --
SELECT Format([Proj_Data].[Start_Date], "yyyymm") AS ProjYRMon,
Format([Proj_Data].[Start_Date], "mmm yyyy") AS Proj_Month, (SELECT
Count([XX].[Project]) FROM [Proj_Data] AS [XX] WHERE
Format([XX].[Start_Date], "yyyymm") = Format([Proj_Data].[Start_Date],
"yyyymm")) AS CountOfProject
FROM [Proj_Data]
WHERE [Proj_Data].[End_Date] Is Null
GROUP BY Format([Proj_Data].[Start_Date], "yyyymm")
ORDER BY Format([Proj_Data].[Start_Date], "yyyymm");

--
Build a little, test a little.


"Mike" wrote:

Is there a way to make an table/query in Access that counts by date?
For example, if I have multiple projects with different start dates,
I'd like to have a running count of the months for the project... e.g.

Start Date 02/28/2010 03/1/2010 05/31/2010
Project A B
C

Date
1/2010 0 0 0
2/2010 1 0 0
3/2010 2 1 0
4/2010 3 2 0
5/2010 4 3 1


Etc. I'm attempting to build this in design mode, but can't think of
an efficient way to do it.
.