PC Review


Reply
Thread Tools Rate Thread

Count within class

 
 
J
Guest
Posts: n/a
 
      2nd May 2008
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!
 
Reply With Quote
 
 
 
 
NetworkTrade
Guest
Posts: n/a
 
      2nd May 2008
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!

 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      3rd May 2008


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!

 
Reply With Quote
 
J
Guest
Posts: n/a
 
      5th May 2008
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!

>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can it possible redefine class with subclass when inherit base class with sub class? ABC Microsoft C# .NET 4 11th Jan 2006 12:13 AM
error LNK2019: unresolved external symbol "public: static class ATL::CStringT<char,class StrTraitMFC<char,class ATL::ChTraitsCRT<char> > > danip Microsoft VC .NET 0 5th Dec 2005 11:01 AM
Getting a reference to the base class of a base class from an inherited class Edward Forgacs Microsoft C# .NET 1 24th Apr 2004 11:06 AM
Pass an instantiated base class to a new class that inherits the same class -- Possible?? Brad Navarro Microsoft C# .NET 3 9th Jan 2004 02:07 AM
activex ,money tree dialer,RdxE Class,updaate class,YInstStarter Class damaged RUSSELL Windows XP Performance 1 23rd Nov 2003 03:05 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:48 AM.