Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create incremental count within groups (Access 2002 SP-2)

    I have a need to create a column in a query which contains an incremental count,
    based on a category, with the count restarting when a new category starts.

    eg.
    Category, Count
    Apple, 1
    Apple, 2
    Apple, 3
    Pear, 1
    Pear, 2
    Pear, 3
    Pear, 4
    etc

    Any help would be appreciated.

    Regards
    Zinger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create incremental count within groups (Access 2002 SP-2)

    Doing this in a query is rather artificial.

    In a continuous form, you might be able to devise a variation on HOW TO: Display Line Numbers on Subform Records in Access 2002.

    In a report, it is actually very easy:
    - Group the report on the category field
    - Put a text box with control source =1 in the detail section, and set its Running Sum property to Over Groups.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Create incremental count within groups (Access 2002 SP-2)

    If you can't follow Hans' suggestion and your table isn't zillions of records in size, see <!post=Number query results by group (2000),250833>Number query results by group (2000)<!/post>.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create incremental count within groups (Access 2002 SP-2)

    Thanks. I hadn't looked up that thread, and I didn't know I was so consistent. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •