Counting Rows based on values in other columns I'm trying to count the rows of an entire table (approx 3000 rows) based on the content of a column. In the column in question, there can be a single text value or a concatenation of text values separated by commas.

To use the student/class analogy, the table could be a list of students, with the column containing one or more classes to which they are assigned.

Thus "Student1" may belong to "class1, class2, class3" (etc..), while "Student2" may only belong to "Class2".

I simply want the student counts of all classes, thus:


Classes Students
Class1    nn
Class2       nn


and so on.

Please note that this is how the data is received (as a csv file) and I don;t really want to manipulate it unless I have to.  I would prefer to simply execute SQL to produce the results, if possible.

Can anyone help?

Thanks,
Geoff
It will look something like this:

SELECT StudentID, SUM(CASE WHEN INSTR(Classes,:Class) THEN 1 ELSE 0 END) 
FROM Classes
GROUP BY StudentID;


This is Oracle syntax, so you will have to translate for Access.