0

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

  • Check out this great answer to better understand why your data format is far from ideal. I'd suggest normalizing your data to be able to do this and pretty much any other data operation far more easily and reliably. – LowlyDBA Feb 3 '17 at 17:32
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.