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 skillDatabase1.zipDatabase1.zip

[jzwp11]

Actually, you would not store the count in the Category table. In most cases, calculated values are not stored. You can get to a solution using a query or actually 2 queries.

Create a query based on the student details table that does the count by skill. You are essentially counting the number of students with each skill. That query would look like this:

query name: qryCountOfStudentsWithSkill
SELECT Student_Details.Skills, Count(Student_Details.Skills) AS CountOfStudents
FROM Student_Details
GROUP BY Student_Details.Skills;

Next, create another query that includes the category table and the query above. Make a left join between the two record sources via the category field of the category table and the skills field of the query. Include the category field and the count field in your query. To take care of the possiblity that no student may have a particular skill, nest an isNull() function within an IIF() function for the count field. That query will look like this:

query name: qryFinal
SELECT Category.Category, IIF(IsNull(qryCountOfStudentsWithSkill.CountOfStud ents),0,qryCountOfStudentsWithSkill.CountOfStudent s) as StudentsWithSkill
FROM Category LEFT JOIN qryCountOfStudentsWithSkill ON Category.Category = qryCountOfStudentsWithSkill.Skills;


In the attached database, I added a new record in the category table that no student currently has listed as a skill to illustrate how the nested functions work to show a 0 value.

BTW, what you did with your original code, could have been done with a simple append query as follows:

INSERT INTO Category ( Category )
SELECT DISTINCT Student_Details.Skills
FROM Student_Details;


Also, it would be better to have autonumber primary key fields in each of your tables. You could then use that primary key field to join to a foreign key field in a related table. Making the joins with the numeric fields is more efficient for a relational database. See the tables that I added to your database (tblStudents, tblStudentDetails, tblCategory) and how they are joined in the Relationship Window. I also repeated the queries using the new design to illustrate. They have the same name as the above queries but with the V2 suffix.