Query that provides a number for each group

[tonydepo]

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!!

[June7]

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.

[Bulzie]

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
etc?

[CJ_London]

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
         Num=1
    Else
         Num=num+1
    End if
    Groupnum=num
end function

in your query create a new column

group number: groupnum(tag)

this assumes that tag is a number and not text