Results 1 to 9 of 9

Add sequential numbering to groups of data

  1. #1
    astephan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    7

    Add sequential numbering to groups of data

    I have a dataset that looks like the below:

    NODE_KEY LOCATION_ID
    A B1D
    A B1E
    A B1F
    B B1G
    B B1H
    C B2D
    C B2C
    C B2D
    C B2E


    How do I add a counter in a new field that is sequential for each NODE_KEY starting at '1'? Example below:

    NODE_KEY LOCATION_ID COUNTER
    A B1D 1
    A B1E 2
    A B1F 3
    B B1G 1
    B B1H 2
    C B2D 1
    C B2C 2
    C B2D 3
    C B2E 4

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,658
    look to the bottom of this thread for the answer to this commonly asked question

  3. #3
    astephan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    7
    Thanks, I checked quite a few other threads but still cannot find a logic that will work for me.... I decided to post my question for this reason.

  4. #4
    astephan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    7
    I got what I needed... worked perfectly.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,212
    If the Location_ID was not duplicated within each Node_Key group, the following should work:

    SELECT Node_Key, Location_ID, DCount("*", "tablename", "Node_Key='" & [Node_Key] & "' AND Location_ID<'" & [Location_ID] & "'") + 1 AS GrpSeq FROM tablename ORDER BY Node_Key, Location_ID;

    However, I see duplicate Location_ID in Node_Key C. Is that valid or a posting error? If it is valid, need another field to serve as unique identifier, an autonumber field will serve.

    SELECT Node_Key, Location_ID, DCount("*", "tablename", "Node_Key='" & [Node_Kaye] & "' AND ID<" & [ID]) + 1 AS GrpSeq FROM tablename ORDER BY Node_Key, ID;

    Now I see you found a solution. Would you care to share?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  6. #6
    astephan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    7
    The Location_ID and Node_Key combo will always be unique... sorry for my poorly constructed example.

    Below solution from another user works pretty well, although it runs extremely slowly if I do a Make Table instead of Select query:

    SELECT
    a.NODE_KEY,
    a.LOCATION_ID,
    (SELECT COUNT(b.NODE_KEY)
    FROM YourTableName AS b
    WHERE a.NODE_KEY = b.NODE_KEY
    AND a.LOCATION_ID >= b.LOCATION_ID
    ) AS MyCount
    FROM YourTableName AS a
    ORDER BY
    a.NODE_KEY,
    a.LOCATION_ID;

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,212
    Yes, nested subqueries and domain aggregates can both be slow performers.

    Alternatively, just generate this sequence number in report where textbox has a RunningSum property.

    What exactly do you need this number for?

    I would avoid a process that implements programmatic db design changes which is what MAKE TABLE does.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  8. #8
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,658
    modifying your code, this should be faster

    SELECT a.NODE_KEY, a.LOCATION_ID, COUNT(b.NODE_KEY) as MyCount
    FROM YourTableName AS b INNER JOIN YourTableName as a ON b.NODE_KEY = a.NODE_KEY AND b.LOCATION_ID <= a.LOCATION_ID
    GROUP BY b.NODE_KEY, b.LOCATION_ID
    ORDER BY b.NODE_KEY, b.LOCATION_ID;

    speed of operation is also improved with indexing - NODE_KEY and LOCATION_ID should both be indexed

    Note this query can only be written in SQL, the query grid will not recognise the location_id join - tho' you can create it with a = join in the query grid, then go into the sql window to modify the join

  9. #9
    astephan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    7
    Thanks Ajax, that did speed up the query significantly. I am good to go. Appreciate everyone's help on this topic.

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

Similar Threads

  1. Daily Reset Sequential Numbering Using Dmax.
    By NickTheG in forum Access
    Replies: 4
    Last Post: 09-20-2017, 12:29 PM
  2. Replies: 1
    Last Post: 06-01-2016, 02:04 PM
  3. Replies: 5
    Last Post: 10-14-2012, 11:18 PM
  4. Sequential Numbering in a Form
    By Falafa in forum Forms
    Replies: 6
    Last Post: 10-05-2012, 07:20 AM
  5. Help with Sequential Numbering
    By orion in forum Programming
    Replies: 3
    Last Post: 07-06-2009, 12:41 PM

Tags for this Thread

Posting Permissions

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