MS Access Running Counter In Query

Подписаться 1,7 тыс.

Просмотров 35 тыс.

50% 1

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

Опубликовано:

31 авг 2013

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 26

@LoeblComServices 5 лет назад

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

@supertiger72 8 лет назад

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

@LoeblComServices 7 лет назад

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

@jassimhashim4706 5 лет назад

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

@naturalfallasleepfast7852 5 лет назад

I am glad it helped!

@LoeblComServices 5 лет назад

You're welcome

@FoxRivers778 4 года назад

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 4 года назад

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

@tizon509 8 лет назад

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

@LoeblComServices 5 лет назад

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

@Deep_Thoughts99 11 лет назад

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

@LoeblComServices 5 лет назад

You need to store the current number in a table

@LoeblComServices 5 лет назад

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

@litmol 5 лет назад

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 5 лет назад

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

@litmol 5 лет назад

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 5 лет назад

@@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 5 лет назад

@@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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-HWbpzETe-M0.html. 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 5 лет назад

@@litmol Perfect, thanks for letting me know.

@jasephine1 9 лет назад

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 9 лет назад

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 9 лет назад

Erik Loebl i can't get u

@jasephine1 9 лет назад

Erik Loebl Can show in the video?

@FoxRivers778 7 лет назад

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

@LoeblComServices 5 лет назад

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

@LoeblComServices 11 лет назад

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. :)