Results 1 to 4 of 4
  1. #1
    tonydepo is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012

    Query that provides a number for each group

    Hi, using Access-2016. I have a table in which I need to perform a query to add a grouping number. First table (TblData) which includes Tag and a Plaza.

    I want to create a query which groups the like tag numbers until a new tag starts, however if the tag repeats later in the data it will group it as a new group number.
    Final results TblFinalQueryResults

    assigning numbers.accdb
    Thanks so much for the help!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Database records have no intrinsic order - think "bolts in a bucket". Access does not know that the "later" tag is actually later or that it is not continuous with the other same tag records unless you tell it so based on some criteria. What you want will likely call for VBA looping through recordset and saving a group number to a field. This requires a unique identifier field that can be relied on to sort records in the order you want. Your table does not have that. Otherwise, cannot guarantee records will maintain order showing in table in a query.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    So you want it to be like this (or can you supply and example of the data how you want it to work):
    Tag, New Counter Value?
    1, 1
    1, 2
    2, 1
    2, 2
    1, 3
    3, 1

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    In a module create the following function- it is air code and written on my phone so may need some refining

    function groupnum(n as long) as long
    static num as long 
        If n<>num then
        End if
    end function
    in your query create a new column

    group number: groupnum(tag)

    this assumes that tag is a number and not text

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

Similar Threads

  1. Replies: 2
    Last Post: 07-07-2021, 02:53 PM
  2. Replies: 5
    Last Post: 09-04-2020, 05:12 PM
  3. Number of records in a group
    By GraeagleBill in forum Reports
    Replies: 4
    Last Post: 04-18-2017, 05:08 AM
  4. Replies: 17
    Last Post: 04-24-2014, 10:58 AM
  5. Replies: 1
    Last Post: 03-12-2009, 09:55 AM

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