Record Counter in Query

[jgabel]

Hello, thank you for your assistance!

In my query called "test" I have the following fields: EmpID & DEPT, below is an example.
EMPLOYEEDEPTcounter1016321101101632125210163222033557031501355703120235570313033557031404

I would like to add a column to my query called "counter" and count starting with 1 each time the employee number is listed, when the employee number changes I would like the counter to start over again at 1 and continue this throughout the query.

Thanks much!

 

[jgabel]

Not sure if grid is displaying properly. I need to add "counter" to my query, below is an example, each time the empID changes I want the record counter to start over again.

EmpID/DEPT/counter
10163/2110/1
10163/2125/2
10163/2220/3
35570/3150/1
35570/3120/2

 

[Cronk]

I don't think you can achieve this without using VBA. However, someone better than me with queries might know a way.

I can see 2 options.

The first, and the one I think I would be using, is to add another field called Counter to your table. Before running the query, populate the Counter field by looping through a record set based on the output of your query to edit the value of Counter.

The second would be to write a function with input EmpID and have static variables to store the results of the previous call to the function ie Counter, and resetting it each time the EmpID changes.

 

[CJ_London]

it can be done within the query but does require some givens:

The basic construction the the query is like this

SELECT EmpID, 
Dept, 
(SELECT COUNT(EmpID) FROM myTable as tmp where EmpID=myTable.EmpID AND [COLOR=red]Dept<=myTable.Dept[/COLOR]) AS Counter 
FROM myTable
ORDER By EmpID, [COLOR=red]Dept[/COLOR]

One given is that given the data you have provided for this to look right you need to sort it by dept which you may not want (It's OK for your first employee without sorting, but not for the second in your example) however you may have another field such as an autoID, timestamp, etc which can referenced in the bits highlighted in red above which would resolve this problem.

The other given is the employee does not have a department more than once - if the employee has two jobs in the same department you will get two records with the same counter value.

However I'm guessing that your data extends further than you have suggested and the query can be easily refined to overcome these.

 

[jgabel]

CJ, that worked perfectly from what I'm seeing in my small testing table. Thank you!

 

[Cronk]

And thanks from me for teaching me something new. I still can't see how "as tmp" has, in the position it has, the effect it does. (My strong suit is definitely not complex Queries.)

 

[CJ_London]

@Cronk - I'm always learning something new

I still can't see how "as tmp" has, in the position it has, the effect it does.

It aliases the table so it can refer 'to itself', could just as easily have used "as myTable_1" or "as X" and within the context of the query since it is a selected item the subquery has to be designed to only return one value - hence the use of count. You can also use max, min etc or top 1. So you could have used the dcount function instead, but this is slow with many records

If the subquery is in the criteria it can return multiple values but for only one column do you can say myValue in (mysubquery).

 

[Cronk]

@ CJ

I did know that. I've used it in Cartesian joins to check time overlaps but somehow when I looked at your sql my thinking was screwed wrong that having the alias after the table was a special placement of the field alias. Duh!

 

[docxyz]

Hi, I have a problem with something like that, I want to create a counter field that is automatically filled and resets itself each new day, of course I dont mean autonumber field "I actually uses autonuber field as visit_id", all I want is a counter starts from 1 each new day, and filled automatically rather than typing it each time, thank you.

 

[CJ_London]

try:

counter: dcount("*","myTable","myDate=Date()")+1

or

CounterSELECT Count(*) FROM myTable as Tmp WHERE myDate=Date())+1

 

[docxyz]

try:

counter: dcount("*","myTable","myDate=Date()")+1

or

CounterSELECT Count(*) FROM myTable as Tmp WHERE myDate=Date())+1

I will try this today, thank you for your Response

 

[docxyz]

try:

counter: dcount("*","myTable","myDate=Date()")+1

or

CounterSELECT Count(*) FROM myTable as Tmp WHERE myDate=Date())+1

Hi CJ_London, it worked perfectly, thank you very much, but i have a questions, if two client PCs opened the entry form at the same time, is there any conflict with counter? I mean any competition for the same number? I hope you understand my quesion.. Thank you.

 

[CJ_London]

potentially - to minimise the risk assign this in VBA in the before update event for the form rather than when the user starts to enter details in a new record - although there is still a risk if two users save at exactly the same time

If you look in this forum you will find plenty of threads which discuss the risk of a 'clash'.

Personally, I don't see the benefit of doing this. I would use an autonumber which is risk free and if I wanted to know the number of entries for the day I would just use the dcount code. If you want to know the order in which records are entered, also have a timestamp field although the autonumber order can usually be relied on

 

[docxyz]

potentially - to minimise the risk assign this in VBA in the before update event for the form rather than when the user starts to enter details in a new record - although there is still a risk if two users save at exactly the same time

If you look in this forum you will find plenty of threads which discuss the risk of a 'clash'.

Personally, I don't see the benefit of doing this. I would use an autonumber which is risk free and if I wanted to know the number of entries for the day I would just use the dcount code. If you want to know the order in which records are entered, also have a timestamp field although the autonumber order can usually be relied on

Dear CJ_London, we have a folder created each day represents the date of today, consists of files which are the visits itself, each file in the folder "must" have a serial number which is the counter in our situation, so this counter idenifies the file "visit" in a specific day, and must be started from one each dey, for this reason i cannot rely on autonumber counter. I know i can count visits of each day very easely but this is not the issue.