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!

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


"J" wrote:

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

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 wrote:
> 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!

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

"John Spencer" wrote:

>
>
> 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 wrote:
> > 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!
>