Record Count

[rob4465]

Hi there - I'm trying to sort out some code to count the occurances of some data in a field.

Basically I have an ID field that has been created from an update query. The result is a couple of thousand names like:

Smith-John
Bloggs-Joe
etc...

What I need is some code to go through each record and at each point in the table I need to know how many occurances of that entry there are.

So for example if at record number 1000 in the table there are 5 occurances of Smith-John I need to update a field called 'Name Count' with the number of occurances and so on.

The count has to reflect the number of occurances at each record in the table as I'm using this information to create another ID field I'm using for something else.

Hope this makes sense.

Thanks very much for your help in advance.

 

[dbDamo]

Let me get this right. Your information is stored like this:-

ID|Name
1|Bloggs-Joe
2|Smith-John
3|Smith-John
4|Bloggs-Jill
5|Bloggs-Joe

And you wish to calculate the number of occurrences of the same name, each time adding 1 to the total? Like this:-

ID|Name|CountofName
1|Bloggs-Joe|1
2|Smith-John|1
3|Smith-John|2
4|Bloggs-Jill|1
5|Bloggs-Joe|2

 

[llkhoutx]

Cycle through your recordset using DAO/ADO code, building a table with the field value and count you want.

 

[Poppa Smurf]

Look up the Help on Total records in a query (MDB)

Create a query and use this SQL code. Replace tbl_test with the name of your table, you may need to change the field name person_name to your field name. This query will give you the count of each group of names.

SELECT tbl_test.person_name, Count(tbl_test.id) AS CountOfid
FROM tbl_test
GROUP BY tbl_test.person_name;