Counting Table (By Date)

[Mike]

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.

 

[KARL DEWEY]

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

 

[KARL DEWEY]

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

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.

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