Count within class

[J]

I have a access table with a mere two vairables one uniqly identifies the
individual and the other identifies the group i.e:

John Doe Alpha
John Roe Alpha
Jane Doe Beta
Jane Roe Beta
Steve Doe Gamma
Steve Roe Gamma
Henry Doe Gamma

I want to create another variable which counts the number of occurences for
each group:

John Doe Alpha 1
John Roe Alpha 2
Jane Doe Beta 1
Jane Roe Beta 2
Steve Doe Gamma 1
Steve Roe Gamma 2
Henry Doe Gamma 3

I had a vb code that did this many years ago, but i have not used in a long
while and i can't seem to find it.
thanks for any help!

 

[NetworkTrade]

would be fairly straight forward as a query using the group/count function....

 

[John Spencer]

SELECT Individual, GroupName
, 1 + (SELECT Count(*) FROM TableName as Tmp
WHERE Tmp.GroupName = TableName.GroupName
AND Tmp.Individual < TableName.Individual) as Rank
FROM TableName

If you are using the query grid, add a calculated field that looks like:
Field: Rank: 1 + (SELECT Count(*) FROM [TableName] as Tmp WHERE
tmp.[GroupName] = [TableName].[GroupName] AND Tmp.[Individual] <
[TableName].[Individual])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[J]

That was perfect! So much cleaner than a VB code too! Thank you so much.

SELECT Individual, GroupName
, 1 + (SELECT Count(*) FROM TableName as Tmp
WHERE Tmp.GroupName = TableName.GroupName
AND Tmp.Individual < TableName.Individual) as Rank
FROM TableName

If you are using the query grid, add a calculated field that looks like:
Field: Rank: 1 + (SELECT Count(*) FROM [TableName] as Tmp WHERE
tmp.[GroupName] = [TableName].[GroupName] AND Tmp.[Individual] <
[TableName].[Individual])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a access table with a mere two vairables one uniqly identifies the
individual and the other identifies the group i.e:

John Doe Alpha
John Roe Alpha
Jane Doe Beta
Jane Roe Beta
Steve Doe Gamma
Steve Roe Gamma
Henry Doe Gamma

I want to create another variable which counts the number of occurences for
each group:

John Doe Alpha 1
John Roe Alpha 2
Jane Doe Beta 1
Jane Roe Beta 2
Steve Doe Gamma 1
Steve Roe Gamma 2
Henry Doe Gamma 3

I had a vb code that did this many years ago, but i have not used in a long
while and i can't seem to find it.
thanks for any help!