We help IT Professionals succeed at work.

Create Incremental Count and Reset Counter for Field Change in Access

Anthony6890
Anthony6890 asked
on
5,075 Views
Last Modified: 2017-03-16
Hi,

I'm trying to figure out a way of adding a row count to a query that adds a sequential row number for each change in a subsequent field.

For example, let says I have a table of values where the employee ID is the main lookup value.  That value is linked to a dependent to determine who the dependent is.  I'm making a table with this data that lists the employee's number followed by the dependent's ID number.  I want the dependent's to become numbered, 1,2,3, and etc until the employee's ID number changes.  I then want the counter to reset.  Is there a way that I can accomplish this?

Here is an example of what I am looking for...

Employee_ID                   Dependent_ID                  Dep_Num
ABC123                             321zax                               1
ABC123                             221zax                               2
ABC123                             721zax                               3
GHI555                              899tti                                 1

Does anyone know how I can accomplish this?

-Anthony
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
DCOUNT is extremely slow when used in a query, so I would warn you against using that technique, as is the nested subquery technique provided by Rey.

What are you using this for, is it a report or do you want to display it in a form?  If displaying it in a form, do you want the form to be editable or simply use the form to view the data?

Author

Commented:
Hi everyone, I haven't been back at my desk yet to try each suggestion, but I will be able to in a couple of hours.

Dale, I'm trying to use this in a table.  I have an append query that outputs the results to a table that can then be used by other people for data manipulation purposes.  The query writes out the depends information and I need this increment number to be appended as well. I had a count function; however, that wasn't producing consistent results.
CERTIFIED EXPERT
Top Expert 2016

Commented:
did you try the query I posted?

storing calculated values is not advisable.

Author

Commented:
Hi Rey, I haven't tried it yet, but I will. Just give me a little time and I'll let you know the results.
Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

Commented:
You might consider adding Dep_Num as a field in your table, and run an update query to repopulate it each time the data changes;  that way your users wouldn't have to run DCount as part of their query, and you'd avoid the potential latency mentioned by Dale.
Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

Commented:
Rey, I would ordinarily agree that storing calculation results is not a good idea.  But there are cases --like this one, where users are connecting to a data store to read static values-- where it is a good way to reduce latency.
CERTIFIED EXPERT
Top Expert 2016

Commented:
you can always use the query to extract updated information instead of getting the info from the table.
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2016

Commented:
the query  was created based on the information posted. it can be altered accordingly if there are more pertinent information provided, such  as date of birth or any historical or hierarchical information.

Author

Commented:
Hi Everyone,

I originally had a module that I found on the internet to do what I detailed above.  The problem I ran into was that sometimes when the below function would run, it would not sequence the people at each change in employee ID change.  At times, it would make two dependents a 1, when they should be a 1 and then a 2.  Pat made me realize that I had to add an order by to my original query and that would allow the function to run correctly.  

Paul, I appreciate your suggestion; however, I couldn't get it to work correctly.

Rey, yours might have also worked; however, upon reading Pat's post it made sense.  

Here is the function that I use...

Option Compare Database

Global EmpNum As String
Global GBL_Icount As Long

Public Function increment(ivalue As String) As Long

If Nz(EmpNum, "zzzzzzzzz") = ivalue Then
        GBL_Icount = GBL_Icount + 1
Else
        EmpNum = ivalue
        GBL_Icount = 1
End If
increment = GBL_Icount
End Function

Open in new window


I then call this function in the query and it works like a charm after I added an order by to the employee ID.

DEPCOUNT: increment([EMPID])

Thanks everyone for your help on this, but the solution was in front of my eye.  I just needed more guidance to finish it.

-Anthony

Author

Commented:
Pat allowed me to review my current query and just add a parameter and the original function now works perfectly.  

Everyone offered great solutions; however, the added work to get them to work didn't compete with the simple fix of the original query.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
I actually use a function to do this, but only if I'm dumping the info into a temp table.  The function looks like:

Public Function fnRank(Reset As Boolean, ParamArray FieldValues() As Variant) As Integer

    Static myRank As Long
    Static myArray() As Variant
    Dim intLoop As Integer
    Dim bIdentical As Boolean
    
    If (Reset = True) Then
        ReDim myArray(UBound(FieldValues))
    End If
    
    bIdentical = True
    For intLoop = LBound(FieldValues) To UBound(FieldValues)
        If (bIdentical = True) And (myArray(intLoop) = FieldValues(intLoop)) Then
            'do nothing
        Else
            bIdentical = False
            myArray(intLoop) = FieldValues(intLoop)
        End If
    Next
    
    If bIdentical = False Then myRank = 0
    
    myRank = myRank + 1
    fnRank = myRank
    
End Function

Open in new window

This function will run amazingly quickly (compared to the other methods mentioned above) and will accomodate as many fields as you choose to use in your ranking function.

And you would use it like:

1.  First you need to call it setting the first argument (Reset) = True and then passing in a value for each of the fields you want to use in the ranking feature.  Since the parameter array and the static array in the function are variants, you don't even need to worry about the values you pass in, just make sure you pass in a value for each column you will pass into the function in your query.  I generally call to this just before running my query.

2.  Then, you would call this as your Rank column in your query, something like the following.  In this case, we want to rank employee dependents based on their DOB, so we use an order by clause that includes both of those fields, but we only need to pass the function the EmployeeID, because we want to reset the rank  whenever the EmployeeID changes.
SELECT EmployeeID, DependentID, fnRank(0, [EmployeeID]) as Rank
FROM yourTable
Order by EmployeeID, DOB

Open in new window

The down side of this technique, is that if you scroll up through the query result set, you will see that the Rank column continues to get recomputed as records move into the set of records which are visible in the query results.  So, if you scroll up, the numbers get screwed up.  To avoid this, I create a Rank table (tblRanks) and actually insert the rank values into that table, along with the unique PK value that identifies each record, something like:
currentdb.execute "DELETE * FROM tblRanks", dbfailonerror
fnRank -1, 0                                                                                                 'This resets the length of the array stored in the function
strSQL = "INSERT INTO tblRanks (PKID, Rank) " _
             & "SELECT ID, fnRank(0, [EmployeeID]) as Rank " _
             & "FROM yourTable "
             & "ORDER BY EmployeeID, DOB"
Currentdb.Execute strsql, dbfailonerror

Open in new window

Then you can join tblRanks to yourTable on the PK field, something like:
SELECT EmployeeID, DependentID, R.Rank
FROM yourTable as T
INNER JOIN tblRanks as R on T.ID = R.PKID
Order by EmployeeID, DOB

Open in new window

If you configure tblRanks so that the PKID field is the primary key of that table, then the query shown above should be updateable.

Sorry I didn't get a chance to post this earlier.

HTH
Dale

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions