Count incrementally each duplicate column value in Access Query

[Rajeev Mohan]

Hi gurus,

I have a query where I need to incrementally count the repetition of column values and add that value in a new column.

Example:
"Column One" and "RepeatCounter" are fields in a query.

Column One Repeat Counter
Value 1 1
Value 2 1
Value 3 1
Value 1 2
Value 1 3
Value 3 2

I want to create the outcome in the field "Counter" in an Access Query.
I am a novice in Access VBA. I found I can do it in Excel with the formula CountIF. I am unable to do it in Access.

Please help.

Rajeev

 

[pr2-eugin]

Hello Rajeev Mohan, Welcome to AWF

What you might be interested is to use the Running sum method, modify the code a bit and you might be able to get what you want.

 

[Bilbo_Baggins_Esq]

are you doing this in VBA or in a Query?

 

[Rajeev Mohan]

thanks Very much! I will Running Total.

And yes I want to use it in a query. Since there is not Countif function in Access, I thought I will have to call a VBA code in the query for this.

 

[Rajeev Mohan]

Hi gurus, I am having a tough time with the Running Total as well.
Running total is actually summing up the values from the first to the current record.

But what I want is very different thing. Every time a column value repeats, I need to add a counter for that occurrence. See my example:

Column One Counter
Value 1 1
Value 1 2
Value 3 1
Value 1 3
Value 1 4
Value 3 2

For every "Column One" value, it should search for how many times it is repeating from the first record to the current record, and add the count in the "Counter " column.


Your help is much appreciated.

Thanks,
Rajeev

 

[Bilbo_Baggins_Esq]

you seem to be describing something different than I thought you were describing originally.

Are you:
1. wanting to count matches for the first record's column 1 value in all other records of the same table or;
2. wanting to count matches for column 1 that exist in the other columns of the same record?

 

[CJ_London]

You need an additional column (perhaps a timestamp or autonumber) which has a unique value in it, at the very least by the value in column 1

ID ColumnOne Counter
1 Value 1 1
2 Value 1 2
3 Value 3 1
4 Value 1 3
5 Value 1 4
6 Value 3 2

then your code would be:

SELECT *, (SELECT COUNT(*) From myTable AS tmp WHERE ColumnOne=myTable.ColumnOne and ID<=myTable.ID) AS Counter
FROM myTable

 

[Rajeev Mohan]

Hey, I am sorry for confusing with my explanations. Let me try to make it clear.

Query: qryschedule
Field: "Coulmn One" - Comes from a table
Field: "Counter" - Manually added in Query Design

I have a query "qrySchedule", which has a column "Column One", which comes from a table.

when I run the query: Every time a record in the "Column One" is repeated, I want to add an incremental counter in the column "Counter" for that record.

So when "value 1" in "Column One" occurs the first time, the "Counter" field for that record should get 1.
The next time "Column One" has "value 1", the "Counter" field for that record should get 2, because this is the second occurrence of "value 1"

This should happen for all values in "Column One".

Column One Counter
Value 1 1
Value 1 2
Value 3 1
Value 1 3
Value 1 4
Value 3 2


Hope I am clear now.


Regards,
Rajeev

 

[CJ_London]

Please can you clarify your terminology. Just repeating the same thing does not help us understand better.

want to add an incremental counter in the column "Counter" for that record.

Do you mean add a new column, add a new record update an existing field or what?
How do you define 'that record'? What is the relationship between 'that record' and the query?

Suggest you show some example data from your query, the sql for the query, the table you want to 'change' and the result you want to achieve - before and after the change.

 

[Adam McReynolds]

Is this what you are looking for Rajeev? Open the form to see if this is the desired result( a sum of 2 fields in the same record).

Attachment