Results 1 to 3 of 3
  1. #1
    BNeff is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2019
    Posts
    3

    Number cells sequentially - Restart with new key

    Hello all,



    I am trying to build a query that will, in a column, number cells from 1-x sequentially down, and restart at 1 for every new key ID

    So for example:

    KeyID Number
    1 1
    1 2
    1 3
    1 4
    2 1
    2 2
    2 3
    3 1
    3 2

    Any advice would be helpful.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This is a very common topic. The easiest approach is to use textbox RunningSum property in report along with Grouping & Sorting features. Calculating in query requires DCount() or a nested subquery. Include the KeyID field in the criteria. Really also need a unique record identifier - autonumber field can usually serve.

    https://accessexperts.com/blog/2015/07/28/ways-to-do-sequential-numbering-in-access/

    A DCount calculation:

    DCount("*","myTable","KeyID=" & [KeyID] & AND id <= " & [id]) + 1 AS Counter
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    An interesting question, and June has given a recommended response.
    However, there is little context with your question, and as usual, I'd like to hear more about why and how this fits in with your basic database requirement. What is the "business" that you are intending to support with this database?

    Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help in Sequential Number that Restart to 1
    By napster75 in forum Access
    Replies: 8
    Last Post: 09-10-2018, 08:35 AM
  2. Replies: 6
    Last Post: 06-21-2018, 12:56 PM
  3. Replies: 4
    Last Post: 03-01-2015, 11:31 AM
  4. Sequentially number records in a table field
    By kenton.l.sparks@gmail.com in forum Programming
    Replies: 4
    Last Post: 04-08-2011, 08:24 PM
  5. Restart auto number after deleting records
    By P5C768 in forum Database Design
    Replies: 1
    Last Post: 09-11-2009, 02:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums