Counting Table (By Date)

  • Thread starter Thread starter Mike
  • Start date Start date
M

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.
 
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");
 
I forgot to add that to get the display you want then use a crosstab query.
--
Build a little, test a little.


KARL DEWEY said:
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 said:
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.
.
Click to expand...
Click to expand...
 
Back
Top