Count within class

J

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

NetworkTrade

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

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

J

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

John Spencer said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Email Report question 4
Export Access Table into multiple Excel Workbooks 1
Access query sum buy column 2
Macro deleting row if duplicates found 5
Countif. 1
Add 1 to Text File 2
SUMIF & IF 5
Query Help Again 1

Top