Count within class

Discussion in 'Microsoft Access VBA Modules' started by J, May 2, 2008.

  1. J

    J Guest

    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!
     
    J, May 2, 2008
    #1
    1. Advertisements

  2. J

    NetworkTrade Guest

    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!
     
    NetworkTrade, May 2, 2008
    #2
    1. Advertisements

  3. J

    John Spencer Guest

    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!
     
    John Spencer, May 3, 2008
    #3
  4. J

    J Guest

    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!

    >
     
    J, May 5, 2008
    #4
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Manuel Lopez

    Error 430, Class does not support Automation, Windows 2000 sp4.

    Manuel Lopez, Jun 30, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    2,673
    Manuel Lopez
    Jun 30, 2003
  2. Lynnette

    Trouble removing Class Object from Code Editor in Access

    Lynnette, Jun 30, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    706
    Stephen Lebans
    Jun 30, 2003
  3. tobesurveyor via AccessMonster.com

    Difference between Form's Class Module and Class Module

    tobesurveyor via AccessMonster.com, Apr 14, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    1,471
    Nikos Yannacopoulos
    Apr 14, 2006
  4. Guest

    How do I display & change table indexes within a class module?

    Guest, Jun 2, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    167
    Guest
    Jun 2, 2006
  5. BusyProfessorFromBremen

    Count Records within a Recordset

    BusyProfessorFromBremen, Jun 5, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    3
    Views:
    261
    Marshall Barton
    Jun 5, 2009
Loading...

Share This Page