Count Function

I have a table that lists years and other fields I want to count the
years but if the years are equal I want it to add 1 each time. for
example

Year count
1990 1
1991 1
1992 1
1992 2
1992 3
1993 1
1993 2
1994 1
1995 1
1996 1
1996 2
1996 3

Any help would be appreciated.

On 24 Mar 2007 17:48:30 -0700, "Chris" <chrislabs12@gmail.comwrote:

This is much easier to do using an ADO or DAO recordset and some VA
code.

-Tom.

Chris

Substitute real table and field names and this should work for you.

Sub CountTheYears()
Dim DB As database
Dim rs As Recordset
Dim intCount As Integer
Dim strYearMark As String
Dim strSQL As String
strSQL = "SELECT * from tblYearsToCount ORDER BY TheYear"
intCount = 1
Set DB = CurrentDb
Set rs = DB.OpenRecordset(strSQL)
Do Until rs.EOF
strYearMark = rs!TheYear
rs.Edit
rs!TheCount = intCount
rs.Update
rs.MoveNext
If rs!TheYear = strYearMark Then
intCount = intCount + 1
Else
intCount = 1
End If
Loop

Set rs = Nothing
Set DB = Nothing

End Sub


Regards

Kevin C






"Chris" <chrislabs12@gmail.comwrote in message
news:1174783709.988164.47760@b75g2000hsg.googlegro ups.com...

In article <1174783709.988164.47760
@b75g2000hsg.googlegroups.com>, chrislabs12@gmail.com says...It seems that you are grouping by year and ranking within year.
If the table also has a unique column to break ties, then maybe
something like this:

SELECT table1.table_id,
table1.year_nbr,
(SELECT COUNT(* )
FROM table1 AS b
WHERE table1.year_nbr = b.year_nbr
AND table1.table_id >= b.table_id) AS
year_count
FROM table1
GROUP BY table1.table_id,table1.year_nbr;

table_id year_nbr year_count
1 1990 1
2 1991 1
3 1992 1
4 1992 2
5 1992 3
6 1993 1
7 1993 2
8 1994 1
9 1995 1
10 1996 1
11 1996 2
12 1996 3

Chris wrote:
If you have an autonumber field (I like autonumbers in tables) then you
could differentiate the years. Assumes Year is a number, not string. Ex;
Select Year, Dcount("year","tablename","year = " & [Year] And ID <= [ID])

The brackets around the field names points to the field value being
processed.