MS Access Running Counter In Query

Download the database at vbahowto.com/ms-access-running...
This video is part of a solution to an interesting question that
I know most developers have faced or are facing... "How can I
number my rows in an Access query?"
...if you need more explanation, go to vbahowto.com , or click the link above.
MS Access Running Counter In Query

[@LoeblComServices]

Hi, do you have any specific questions about the contents of this video? Let me know in the comments below!

[@jassimhashim4706]

That's what I'm looking for its really useful and I need this alot in my projects thanks a million,

[@naturalfallasleepfast7852]

I am glad it helped!

[@LoeblComServices]

You're welcome

[@FoxRivers778]

Interesting I found I asked a question two years ago but I had a totally different purpose for this. Now I want to simply number the rows in the query. The "counter" field did number the rows but they were not in the order I wanted them sorted, which was not by autonumber "ID" or the order I entered the records. Therefore, I did something similar as you with an separate insert table. I made the append query sort the records as I wanted. They now get an autonumber "ID" in the order I want. Then I based the increment number query on the "ID" of the insert table. Therefore in my case I do not want to insert the "counter" field to autonumber "ID" in the insert table. So we can agree that the increment query only works if you want records sorted in the order they were entered. Also I won't be able to reset the autonumber "ID" in the insert table to 1, which I can live with since I'll just hide it.

[@LoeblComServices]

Sounds like quite a few steps. That got me thinking a bit, so I created another example: vbahowto.com/an-easy-vba-counter/

[@LoeblComServices]

I don't know of a way right now. The count increments per number of rows in the dataset. If someone else would like to offer a suggested solution go right ahead. :)

[@supertiger72]

I want to create a button to tally an office visit like a session and one for groups is thus possible?

[@LoeblComServices]

Try this vbahowto.com/mouse-click-counter-on-access-form/

[@tizon509]

Hello ! Thanks for this video. How can i do the same after a 'SELECT' result in my database ?

[@LoeblComServices]

You could use a QueryDef, and insert the SELECT statement as part of the contents of the QueryDef's query.

[@litmol]

After running your solution in different databases, I found out that it only works if there are no duplicate values within the Number field (in your tblRunningSum). Once there are duplicate values, the row numbering gets corrupted. Any idea how to fix this? I’m desperately trying to find a solution for numbering row in a query when there are duplicates values in my main field.

[@LoeblComServices]

Hi Tai, this is the way I would probably handle this situation, vbahowto.com/ms-access-running-count-with-duplicates/

[@litmol]

Hi Erik,First of all, I would like to thank you for commenting so quickly on my question. I didn’t have high expectations for getting an answer, and certainly not in few hours.Secondly, I applied your solution in your tblRunningSumDups and it works like a charm!Can you please advise whether your code can be modified in order to achieve the following result: I would like to add row counter to a query (not a table), which is called qryWeekly. qryWeekly combines fields from several tables, and it’s later being used to generate a report. There is one field in the query - MainID (a text field, that has duplicates values) - which is never null, so I would like to base the row numbering on that field.Is it possible?I would like to thank you in advance for your support.Tal

[@LoeblComServices]

@@litmol Hi Tal, You are not going to be able to do the counter on your text field with duplicates. The counter works on numeric values. What you could do is make a table with your resulting query, with the new counter field, produce the counter with the code from the link and base your report on the newly formed table. Click the link for an example vbahowto.com/ms-access-running-count-with-string-column-and-duplicates/

[@litmol]

@@LoeblComServices Hi Erik Thanks for your new code. I tested the previous code that you wrote (vbahowto.com/ms-access-running-count-with-duplicates/) with text field and it worked fine (row were numbered correctly). The issue I was facing is how to implement your code in a query instead of a table… I was able to find another code that someone else wrote, which enables to add row numbers to a query. Here is the link to the code that I found: kzbin.info/www/bejne/fojFoa17icpgg5I. My knowledge in VBA is VERY limited so I don't understand the logic behind that code, but it does work. One last thing - please be advised that the CAPTCHA image on your website (vbahowto.com/vba-howto-contact-form/) is not functioning, which makes it impossible to communicate with you from within your website. Once again - thank you for being so responsive and supportive.

[@LoeblComServices]

@@litmol Perfect, thanks for letting me know.

[@Deep_Thoughts99]

how can i found a next available no in sequence? if the numbers are missing from the list

[@LoeblComServices]

You need to store the current number in a table

[@LoeblComServices]

It possibly would be better if you generated the numbers with VBA as per this post: vbahowto.com/ms-access-running-count-with-duplicates/

[@jasephine1]

my ID was run, actually is from 1,2,3....10 However, now is 1,11,12,2,21,22,3,31,32,33. How to solve it?

[@erikloebl5213]

You can convert your result to an integer value by enclosing the resulting value with CInt(), that should change the result to a sortable value.

[@jasephine1]

Erik Loebl i can't get u

[@jasephine1]

Erik Loebl Can show in the video?

[@FoxRivers778]

In your expression DCount("*","tblRunningSum","number

[@LoeblComServices]

This post may answer your question, vbahowto.com/ms-access-running-count-with-string-column-and-duplicates/