Results 1 to 4 of 4

Sequential Numbering based on Criteria

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

    Sequential Numbering based on Criteria



    MLO_ID Tally_1
    1 1
    1 2
    1 3
    1 4
    2 1
    2 2
    3 1
    3 2
    3 3

    I have a database with the MLO_ID column shown above filled in. It can have between 3-100 repeated values - I've kept it rather short here, but suffice to say it's a long column. I want a query that fills in the Tally column as shown, sequentially numbering up for each SAME MLO_ID value, and restarting at every new one.

    Currently I do this by copying the MLO_ID into excel, with the MLO_ID header in A1, and in B2 writing the formula 'IF(A2=A1,B1+1,1)', and then I copy the values back into this Tally_1 column.

    However I would like this in an access query so I can hand it off to coworkers without having to explain extra steps. Is this possible?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,888
    Requires a unique identifier field in table - autonumber should serve. One way uses DCount() domain aggregate.

    SELECT *, DCount("*", "tablename", "MLO_ID=" & [MLO_ID] & " AND [ID]<" & [ID]) + 1 AS Tally FROM tablename;
    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
    BNeff is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2019
    Posts
    3
    That worked very well!

    So MLO_ID should have been MLI_ID, MLO_ID is actually the unique field.

    This brings up a table.. I need to figure out how to update an existing column, as software on top of this needs to use that column to name files in a specific way - so I can't just have a temporary queried table, I need an actual update to the column involved. But I imagine I can just throw an update in there to update the pre-existing Tally_1 field?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,888
    Should work. Give it a try, maybe on copy of table first.

    What software?
    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.

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

Similar Threads

  1. sequential numbering with criteria
    By Harmoesh in forum Queries
    Replies: 1
    Last Post: 07-04-2018, 04:16 PM
  2. Add sequential numbering to groups of data
    By astephan in forum Access
    Replies: 8
    Last Post: 06-07-2018, 07:34 AM
  3. Daily Reset Sequential Numbering Using Dmax.
    By NickTheG in forum Access
    Replies: 4
    Last Post: 09-20-2017, 12:29 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

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