How to count the number of times an item might be selected in a list? (1 Viewer)

sbrown106

Member
Local time
Today, 21:52
Joined
Feb 6, 2021
Messages
77
Hi - I have created a query that sums the number of times different selections are made from a dropdown box in a form, this accumulates over the course of a year. I was wondering if its possible to display this value in the form as a live running total. So that when other users make a selection the change in total will appear (I could then reset this at the end of the day), so it would be a like a counter of those events (or even if its once at the beginning or end of the day). I have used task scheduler to email the morning and afternoon totals to users, but was wondering if its possible to have something visible in the database.

Is something like this available in Access?

Thanks for any ideas or pointers as to how I could start this
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:52
Joined
Aug 30, 2003
Messages
36,118
You could have a DLookup() getting the value from the query as the control source of a textbox. You may need to use the timer event of the form to requery the textbox periodically.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:52
Joined
May 21, 2018
Messages
8,463
I have created a query that sums the number of times different selections are made from a dropdown box in a form,
In the afterupdate event of the combobox you could run an insert query
TblSelections
controlName ' name of control
controlSource ' name of field
Value ' value selected
dateChosen ' date time chosen
UserID ' User id

Now you have a running log of who chose what from what control on date time.
You can display that on a subform for just the current date or sum type of aggregate query.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 19, 2002
Messages
42,976
Not sure why you would care about this but if the combo is bound, you would count instances from the table and not have to store the information elsewhere.

Select SomeCode, Count(*) As CodeCount
From YourTable
Group by SomeCode;

If you wanted to, you could join this query to the table used for the combo's RowSource and display it in the combo or even sort the selections ascending or descending by the count.
 

sbrown106

Member
Local time
Today, 21:52
Joined
Feb 6, 2021
Messages
77
Not sure why you would care about this but if the combo is bound, you would count instances from the table and not have to store the information elsewhere.

Select SomeCode, Count(*) As CodeCount
From YourTable
Group by SomeCode;

If you wanted to, you could join this query to the table used for the combo's RowSource and display it in the combo or even sort the selections ascending or descending by the count.
Hi Pat,
I need a way of counting how many times particular selections are made in a combo box on a form to keep track of progress during the day.
So I have a table (tblCounter), and when a selection is made from the combobox a counter is incremented in the table then when the form is loaded the number is updated in the label. What I would like is to be able to update the caption without having to leave the form to do it. There is probably a much easier way of doing this. This my code

Code:
'''''' in the Form Open Event
Private Sub Form_Open(Cancel as integer)
Dim lngCounter As Long

'get counter

lngCounter = Nz(DLookup("log_id", "tblCounter"), 0)

'update the label
Me.lblCounter.Caption = lngCounter
DoEvents

End Sub


''''''''' in the Properties (Event) of Combo box''''
Private Sub txtStatus_Click()
Dim lngCounter As Long
Dim strUpdateSQL As String


' get counter from table

If Not IsNull(Me!Status) Then

    If Me!txtStatus = "Yes" Then
    
    lngCounter = Nz(DLookup("log_id", "tblCounter"), 0)
    
    lngCounter = lngCounter + 1
    
    'update
    
    strUpdateSQL = "UPDATE tblCounter SET [log_id]=" & lngCounter
    'strUpdateSQL "UPDATE tblCounter SET [log_id]= " & Me.lblCounter & " ;"
    
    CurrentDb.Execute strUpdateSQL
    
    End If

End If
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:52
Joined
Aug 30, 2003
Messages
36,118
As mentioned, why not make the control source your DLookup() rather than setting it in code, and then requery the textbox in the timer event of the form. Or put your code in the timer event with whatever interval is appropriate.
 

sbrown106

Member
Local time
Today, 21:52
Joined
Feb 6, 2021
Messages
77
As mentioned, why not make the control source your DLookup() rather than setting it in code, and then requery the textbox in the timer event of the form. Or put your code in the timer event with whatever interval is appropriate.
sorry ive not used this before, I'm getting confused with the Ontimer and timer interval events. So if i put the code below into the timer event - how do I get this to update at a particular time interval without having to leave the form?
Code:
Private Sub Form_Timer()

Dim lngCounter As Long
Dim strUpdateSQL As String

' get counter from table

If Not IsNull(Me!txtStatus) Then

    If Me!txtStatus = "Yes" Then
    
    lngCounter = Nz(DLookup("log_id", "tblCounter"), 0)
    
    lngCounter = lngCounter + 1
    
    'update
    
    strUpdateSQL = "UPDATE tblApptCounter SET [log_id]=" & lngCounter
    
    CurrentDb.Execute strUpdateSQL
    
    End If

End If

End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:52
Joined
Aug 30, 2003
Messages
36,118
The timer interval is not an event, it's where you control how often the timer fires:


So put a value there for how often you want the event to fire. Once a minute would be 60000.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 19, 2002
Messages
42,976
What happens when someone clicks on the combo? is their selection stored? If it is stored, then do what I said and use the table where the data is stored and count from there. There is no need to maintain another counter. If you want to display the current count you can use a dCount() in the form's open event and again in a timer event if you want to update the form if it stays open.

Just keep in mind that the timer takes resources. Not much but it essentially keeps counting intervals until it is time to activate and do something.

Be VERY careful when modifying code in ANY object if the timer is running. This has the potential to corrupt your project. So, if you have timer events, add a button on your menu that is only visible when YOU are logged in and make sure to turn the timer off whenever you are not specifically testing the timer code so you don't accidently get clobbered.
 

sbrown106

Member
Local time
Today, 21:52
Joined
Feb 6, 2021
Messages
77
What happens when someone clicks on the combo? is their selection stored? If it is stored, then do what I said and use the table where the data is stored and count from there. There is no need to maintain another counter. If you want to display the current count you can use a dCount() in the form's open event and again in a timer event if you want to update the form if it stays open.

Just keep in mind that the timer takes resources. Not much but it essentially keeps counting intervals until it is time to activate and do something.

Be VERY careful when modifying code in ANY object if the timer is running. This has the potential to corrupt your project. So, if you have timer events, add a button on your menu that is only visible when YOU are logged in and make sure to turn the timer off whenever you are not specifically testing the timer code so you don't accidently get clobbered.
Thanks very much Pat for that reply I will look it again. I have the counter working, with the counts in another table (which I will change from what you say). It’s the resetting the counter now I am trying to make sensible to reset after work hours. Will try to look at it again with your suggestions in mind.Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 19, 2002
Messages
42,976
It is really much easier for us to help when you answer questions:(
 

sbrown106

Member
Local time
Today, 21:52
Joined
Feb 6, 2021
Messages
77
It is really much easier for us to help when you answer questions:(
Yes it is! When somebody clicks on the combo the selection is stored in a table, there are half a dozen users of this database at the moment so I am trying to provide daily counts for this particular selection, but the counter needs to be reset and I was trying to do that automically with a timer, so may change that from what you say
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 19, 2002
Messages
42,976
When does the count need to be reset? Do you want it reset daily? Is there an update date value in the table? You can use that for the reset. You can add a date to the table if it doesn't already exist. You need to add a line of code in the FORM's BeforeUpdate eent to populate it before the record gets saved.

I don't know how busy your system is but using your method with the new table to save the running count can cause inaccurate results in a multi-user environment.
 

sbrown106

Member
Local time
Today, 21:52
Joined
Feb 6, 2021
Messages
77
When does the count need to be reset? Do you want it reset daily? Is there an update date value in the table? You can use that for the reset. You can add a date to the table if it doesn't already exist. You need to add a line of code in the FORM's BeforeUpdate eent to populate it before the record gets saved.

I don't know how busy your system is but using your method with the new table to save the running count can cause inaccurate results in a multi-user environment.
The count needs to be reset daily, there isnt an update date value in the table at the moment. I will move the counter I created into the table which uses the combo for selection, and delete the table I created just for the counter. Is it wise/possible to reset that update value in the table automatically? something like if current day of the week = 'Monday' then change update date? I could try that
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 19, 2002
Messages
42,976
It is NOT wise at all to store a number you can easily calculate. PERIOD. That isn't what I suggested. A saved number like this is easily out of date. For example, you want to reset the value each day to start from 0. How are you planning on doing that if the number is stored. If you leave it to the procedure that runs when you update the value, everyone will be seeing YESTERDAY's count until the first person makes an update that causes the count to reset.

You don't care at all about day of the week.

Select Sum(Iff(Status = "Yes", 1,0) as Count
From YourTable
Where TheNewDateField = Date()

Also, is status really a text field? If it is a YN field, then you need to check for true or false or -1 or 0, NOT the string that you elected to display.
 

sbrown106

Member
Local time
Today, 21:52
Joined
Feb 6, 2021
Messages
77
It is NOT wise at all to store a number you can easily calculate. PERIOD. That isn't what I suggested. A saved number like this is easily out of date. For example, you want to reset the value each day to start from 0. How are you planning on doing that if the number is stored. If you leave it to the procedure that runs when you update the value, everyone will be seeing YESTERDAY's count until the first person makes an update that causes the count to reset.

You don't care at all about day of the week.

Select Sum(Iff(Status = "Yes", 1,0) as Count
From YourTable
Where TheNewDateField = Date()

Also, is status really a text field? If it is a YN field, then you need to check for true or false or -1 or 0, NOT the string that you elected to display.
Thanks Pat - I will have a look tomorrow - that looks a lot simpler that what I was attempting! Status is a text field, so I just need to introduce a date field in the table, so users to enter the date at which the selection has been made, make the selection - then I can sum the responses for that date using something like that query above - I hope that's what you mean?!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:52
Joined
Feb 19, 2002
Messages
42,976
You're welcome. I am a firm believer in the KISS method.
 

Users who are viewing this thread

Top Bottom