How to count values in a table

[nervespy]

Hello

I have a table in MS Access with 2 Columns
Column 1: Name
Column 2: Count

i.e.

Name Count
Tim 0
Tim 0
Tim 0
Bob 0
Tim 0
Bob 0

I want VB to run through the Name column and work out how many times each repeated value i.e. Bob or Tim occurs.
Then input that frequeny number in the Count column for each given value.

i.e.

Name Count
Tim 4
Tim 4
Tim 4
Bob 2
Tim 4
Bob 2

I think I will need to use an array to hold and calculate the data, then put the data back into the table from the array, but my knowledge is limited.
Does anyone know how to do this?

 

[Norie]

You could do it with queries.

The first query needed would be to count the occurences:

qryCount

SELECT MyTable.Name, Count(MyTable.Name) AS CountOfName INTO tblCount
FROM MyTable
GROUP BY MyTable.Name;

This will create a table with the counts.

This query would then update the original table:

qryUpdate

UPDATE tblCount INNER JOIN MyTable ON tblCount.Name = MyTable.Name SET MyTable.[Count] = [CountOfName];

 

[Corticus]

Try the following SQL:

SELECT tblName.name, DCount("[name]","tblName","[name]= '" & [name] & "'") AS ct
FROM tblName;

Replace "tblName" with the name of your table, and replace "name" with the name of the field that hold the name.

This is a dynamic query, which is better than storing the counts in a table. It is dangerous to store calculated values in a table since there is no way to "capture" a user changing a precedant value, therefor you never know if your values are current.