Count years in date range

[Guest]

Hello, all. I have two columns in a table; one name StartDate, the other
EndDate. Each table refers to a user and details, obviously, their start
date and end date. Those who have not ended have a null value. To the
question...

I need to count the number of users in each year. For example:

User 1: Start Date: 04/15/1990, End Date: 06/08/1995
User 2: Start Date: 09/08/1993, End Date: 04/25/1995
User 3: Start Date: 10/15/1995, End Date: 12/06/1996

The year count should be: 1990 (1), 1991 (1), 1992 (1), 1993 (2), 1994 (2),
1995 (3), 1996 (1).

My user count is 2,000+. Any help would be greatly appreciated.

 

[Guest]

Use this in a query:

First Column (this will group you users by year)

Field: format$([table name].[End Date],'yyyy')
Table:
Total: Group By

Second Column (this will count the amount of users in that year)

Field: format$([table name].[End Date],'yyyy')
Table:
Total: Count

Hope this helps!

 

[Guest]

Try this --
Build a table named Count with one field - long interger. Put number 1
through the maximum number of years you will have between StartDate and
EndDate.

First query --
SELECT DatePart("yyyy",[startDATE])+[count] AS Expr1
FROM yourtable, [Count]
GROUP BY DatePart("yyyy",[startDATE])+[count]
HAVING (((DatePart("yyyy",[startDATE])+[count])<=DatePart("yyyy",Date())));
This will create a list of every possible years between the oldest StartDate
and today.

Second query --
SELECT FirstQuery.Expr1, Count(yourtable.user) AS CountOfID
FROM yourtable, Query27
WHERE (((Query27.Expr1) Between DatePart("yyyy",[startDATE]) And
DatePart("yyyy",[endDATE])))
GROUP BY Query27.Expr1;

This was test on my table and names edited. Hope I did not make a mistake
in editing.

 

[Guest]

Karl

I prefer to have a [Numbers] table, that has one column (long integer)
labeled [lngValue]. I only include the numbers 0 through 9 (this keeps the
database smaller). I then create a query (qry_Numbers) that looks like below
to get values from zero to 999.

SELECT Hundreds.lngValue * 100 + Tens.lngValue * 10 + Ones.lngValue as
lngNumber
FROM [Numbers] Hundreds, [Numbers] Tens, [Numbers] Ones

With this table and query, you should be able to write this:

SELECT Year([StartDate])+[lngNumber] AS EmployeeYear,
Count(tbl_Employees.ID) AS Employees
FROM qry_Numbers, tbl_Employees
WHERE (((Year([StartDate])+[lngNumber])<=Year([EndDate])))
GROUP BY Year([StartDate])+[lngNumber]
ORDER BY Year([StartDate])+[lngNumber];

HTH
Dale

Try this --
Build a table named Count with one field - long interger. Put number 1
through the maximum number of years you will have between StartDate and
EndDate.

First query --
SELECT DatePart("yyyy",[startDATE])+[count] AS Expr1
FROM yourtable, [Count]
GROUP BY DatePart("yyyy",[startDATE])+[count]
HAVING (((DatePart("yyyy",[startDATE])+[count])<=DatePart("yyyy",Date())));
This will create a list of every possible years between the oldest StartDate
and today.

Second query --
SELECT FirstQuery.Expr1, Count(yourtable.user) AS CountOfID
FROM yourtable, Query27
WHERE (((Query27.Expr1) Between DatePart("yyyy",[startDATE]) And
DatePart("yyyy",[endDATE])))
GROUP BY Query27.Expr1;

This was test on my table and names edited. Hope I did not make a mistake
in editing.

Hello, all. I have two columns in a table; one name StartDate, the other
EndDate. Each table refers to a user and details, obviously, their start
date and end date. Those who have not ended have a null value. To the
question...

I need to count the number of users in each year. For example:

User 1: Start Date: 04/15/1990, End Date: 06/08/1995
User 2: Start Date: 09/08/1993, End Date: 04/25/1995
User 3: Start Date: 10/15/1995, End Date: 12/06/1996

The year count should be: 1990 (1), 1991 (1), 1992 (1), 1993 (2), 1994 (2),
1995 (3), 1996 (1).

My user count is 2,000+. Any help would be greatly appreciated.