VB to calculate count of distinct values

[gokul1242]

hi i currently have a student_Details ms access table with the fields - "ID" and "Skills".and another table called Category with field "category" which stores the distinct skills of the students
I have used the following VB code to calculate the distinct skills from the Student_details table

Option Compare Database

Private Sub Command0_Click()
strSQL = "select distinct(Skills) from Student_Details"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF
        If rs(0) <> Null Then  'The real loop exit condition.
            Exit Do
        End If
        Debug.Print (rs(0));
        DoCmd.SetWarnings (False)
        a = "insert into Category values ('" & rs(0) & "');"
        DoCmd.RunSQL a
         DoCmd.SetWarnings (True)
        rs.MoveNext
    Loop

End Sub

Now i need to calculate the count of the distinct skills from the Student_details table and store it in the Category table as a column corresponding to each distinct skill.




View attachment Database1.zip

 

[plog]

First and foremost, do not store calculated values in a table. Whatever the count of distinct skills is, you don't store it in a table, you run the query that generates them whenever you need that data.

Second, why VBA? Getting a distinct count of data requires a query, possibly with a sub query.

If you would like to post some sample data from the tables along with what the expected result is from that sample data I will help you build a query to generate your distinct count.

 

[gokul1242]

First and foremost, do not store calculated values in a table. Whatever the count of distinct skills is, you don't store it in a table, you run the query that generates them whenever you need that data.

Second, why VBA? Getting a distinct count of data requires a query, possibly with a sub query.

If you would like to post some sample data from the tables along with what the expected result is from that sample data I will help you build a query to generate your distinct count.

hi i have attached the sample file in my previous post. I want to store the count values in the table because i am going to generate a report based on the skill , so only

 

[plog]

Again, you don't store calculated values, you calculate them when you need them.

Here's a link to an aggregate query tutorial: http://www.599cd.com/tips/access/aggregate-query/

That is the type of query you need to get the count of values in a table.