Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Create Incremental Count and Reset Counter for Field Change in Access
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
using a query
SELECT Table1.Employee_ID, Table1.Dependent_ID, (select count (*) from table1 as T where T.Employee_ID=Table1.Emplo
FROM Table1
ORDER BY Table1.Employee_ID, Table1.Dependent_ID;
SELECT Table1.Employee_ID, Table1.Dependent_ID, (select count (*) from table1 as T where T.Employee_ID=Table1.Emplo
FROM Table1
ORDER BY Table1.Employee_ID, Table1.Dependent_ID;
Use the DCount() function to count the rows where the EmployeeID is the same as the current row's EmployeeID and the DependantID is greater than the the current row's DependantID, and then add 1 to the result so you don't have any Dep_Num fields returning a zero.
SELECT Employee_ID, Dependant_ID,
DCount("Dependant_ID","Tem
FROM tempDemoIncrementerTb;
If you're not familiar with the SQL view of a query, build it as you normally would, adding EmployeeID and DependantID to the grid, and then paste
Dep_Num: DCount("Dependant_ID","Tem
into the third column.
The DCount() ("Domain Count") function has three arguments: Expression, Domain, Criteria. The first argument is what you are counting, the second is where it is being counted, and the third is the criteria that determines whether an Expression is included in the count. (The critieria argument is the equivalent of the Where statement in SQL.) In this example, we are concatenating references to the data fields in the domain as part of the criteria string; when the expression is compiled, the criteria statement becomes "Employee_ID = 'ABC123' and Dependant_ID > '321zax')+1".
Note that you have to bracket the [Employee_ID] and [Dependant_ID] with single quotes, because their values are text; if their values were numeric, the single quotes would not be needed.
SELECT Employee_ID, Dependant_ID,
DCount("Dependant_ID","Tem
FROM tempDemoIncrementerTb;
If you're not familiar with the SQL view of a query, build it as you normally would, adding EmployeeID and DependantID to the grid, and then paste
Dep_Num: DCount("Dependant_ID","Tem
into the third column.
The DCount() ("Domain Count") function has three arguments: Expression, Domain, Criteria. The first argument is what you are counting, the second is where it is being counted, and the third is the criteria that determines whether an Expression is included in the count. (The critieria argument is the equivalent of the Where statement in SQL.) In this example, we are concatenating references to the data fields in the domain as part of the criteria string; when the expression is compiled, the criteria statement becomes "Employee_ID = 'ABC123' and Dependant_ID > '321zax')+1".
Note that you have to bracket the [Employee_ID] and [Dependant_ID] with single quotes, because their values are text; if their values were numeric, the single quotes would not be needed.
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?
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?
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.
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.
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.
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.
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.
you can always use the query to extract updated information instead of getting the info from the table.
As Rey mentioned, storing a calculated value is not advisable and using DCount() to determine the next available number makes a very dangerous assumption. That being that no child record can ever be deleted, even if it was entered in error. Rey's first suggestion, counts the records on the fly and assigns a sequence number. Although this method might result in different children being assigned the same number in different exports. If you are going to assign a sequence number on the fly, you need to use something ti force the recordset into the same order each time. Perhaps descending by DOB or ascending by autonumber to at least always return the dependents in the same order.
In relational database theory, recordsets (tables/queries) are unordered sets. That means that the database engine might return the rows in a different order at a different time unless you expressly sort them using an Order By Clause.
Access fools people into thinking that records don't move once they are added to a table but they can if you update them and the record becomes too big to place back where it was originally. One of the jobs of the Compact tool is to reorder every table into primary key sequence so tables that got moved out of order, generally get put back in line after a Compact. But you cannot ever rely on consistent order unless you sort the recordset so always make it a practice to do that when you need to rely on a specific order or want the order to remain consistent.
In relational database theory, recordsets (tables/queries) are unordered sets. That means that the database engine might return the rows in a different order at a different time unless you expressly sort them using an Order By Clause.
Access fools people into thinking that records don't move once they are added to a table but they can if you update them and the record becomes too big to place back where it was originally. One of the jobs of the Compact tool is to reorder every table into primary key sequence so tables that got moved out of order, generally get put back in line after a Compact. But you cannot ever rely on consistent order unless you sort the recordset so always make it a practice to do that when you need to rely on a specific order or want the order to remain consistent.
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.
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...
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
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
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
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.
Everyone offered great solutions; however, the added work to get them to work didn't compete with the simple fix of the original query.
I actually use a function to do this, but only if I'm dumping the info into a temp table. The function looks like:
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.
Sorry I didn't get a chance to post this earlier.
HTH
Dale
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
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
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
SELECT EmployeeID, DependentID, R.Rank
FROM yourTable as T
INNER JOIN tblRanks as R on T.ID = R.PKID
Order by EmployeeID, DOB
Sorry I didn't get a chance to post this earlier.
HTH
Dale
Premium Content
You need an Expert Office subscription to comment.Start Free Trial