Count key for a table (1 Viewer)

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.

NT100 said:

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

Click to expand...

Why VBA, why not a query?

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"

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.

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

or

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'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

This is not an appropriate use for DCount(). To get the counts NT100 asked for would require either hard coding all the IDs or creating another look to run the domain function for each row in a table. Both are just plain silly solutions.

@NT100, apparently the original suggestion by MajP was too subtle.

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.

Click to expand...

This conflicts with what you originally asked for.

Do you want a set of ALL counts that you can export (you would not make a new table to do this, you would export the query) - that is what you originally requested OR do you want individual counts.

It is very hard for us to help you if we have a moving target. You have two answers. The Dcount() would work (even though I wouldn't use it) if you want a single count. The totals query would work if you want a list.

Select TRef, Count(*) AS DupCount
From yourtable
Group by TRef;

OR - if you want a count of only a single TRef, put an unbound control on your form and reference it.

Select TRef, Count(*) AS DupCount
From yourtable
Where TRef = Forms!yourform!cboTRef
Group by TRef;

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.

Click to expand...

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.