VB to calculate count of distinct values

gokul1242 · October 3rd, 2012, 01:24 PM

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

Code:

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