Count key for a table

[NT100]

Hi,

I've a table containing a number of records with duplicated keys like that shown below

TRef TTID
1 11
1 24
1 6
2 9
2 14
3 8

I would like to count each TRef in that table and write them into another table as summaried below with MS ACCESS VBA

TRef Count
1 3
2 2
3 1

Any suggestions are welcome.

 

[JHB]

..
I would like to count each TRef in that table and write them into another table as summaried below with MS ACCESS VBA
..

Why VBA, why not a query?

 

[MajP]

A group by query will do that without any code
https://www.w3schools.com/sql/sql_groupby.asp
Then you can use that query in an insert query if going to an existing table
http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql_insert_into_select.asp.html
Or if making a new table
https://www.w3schools.com/sql/sql_select_into.asp

If you want VBA to run your query once you make it then
currentDB.execute "YourQueryName"

 

[NT100]

I need to build a VBA script to pass value of TRef (e.g. 2) to the query parameter to get the count for that TRef. It's 2 in this case.

 

[isladogs]

Fine. Use an aggregate query for this as MajP has already suggested. This will always be up to date.
Creating a new table is unnecessary and it would require updating every time a new record is added to your first table

 

[MajP]

Public Function GetTREF_Count(TREF as Long) as long
  GetTREF_Count = dlookup("NameOfCountFieldInAggregate","NameOfAggregateQuery","TREF = " & TREF") 
end function

or

Public Function GetTREF_Count(TREF as Long) as long
  GetTREF_Count = dcount("*","NameOfTREF_Table","TREF = " & TREF") 
end Function

 

[NT100]

Use of Dlookup is what I did before.

I wonder if I can make use of query parameters in VBA. I've skimmed querydef but don't know how to apply it.

I would be appreciated if any hints are suggested.

 

[isladogs]

You've already been given more than a hint. You've been supplied with a function. DCount would do what you want. Use that.
Or use an aggregate query

You are trying to make this more complicated than it needs to be

 

[MajP]

Use of Dlookup is what I did before.
I wonder if I can make use of query parameters in VBA. I've skimmed querydef but don't know how to apply it. I would be appreciated if any hints are suggested.

You have been given basically the same solutions from multiple highly experienced Access developers. Either you are not understanding us or we are not understanding you. Do not tell us how you want the solution because so far your solutions make no sense. Clearly explain how you want to execute this in case we are missing something. From a form, from a query, from a command button and then how you want it returned. A list, a single value. But nothing so far from what you say suggests why an aggregate query is not feasible to get the list of all counts (or provided a criteria a single value) or why a dlookup is not feasible to return a single value.