CODEWITHSUNDEEP
sqlms-accessvba
ogo
ogo

Reputation: 15

How do I select students who have missed a specific class 3 or more times?

I have a table called "tbl1Assiduidade" that has the students' attendance. That table has the student's name, date, class, attendance. It gets these values from other tables and saves them on these respective rows: Nome,Data,UFCD,Assiduidade. The row "Assiduidade" (attendance) has pre-defined values:

If the student is present in the class, then "Assiduidade" = 1 If the student is missing, then "Assiduidade" = 2

If a student misses the same class 3 times, he then fails that class

What I'm trying to do is make some kind of filter, so that whenever i press a button, it will show me the name of the student and the class they failed. If no student has failed any class, then obviously nothing would appear.

There's obviously something wrong with my code, if not everything, I've searched a lot and haven't found what I'm looking for, so thanks in advance for the help!

Private Sub Comando192_Click()

Me.RecordSource = "SELECT tbl1Assiduidade.Nome, tbl1Assiduidade.UFCD 
FROM tbl1Assiduidade 
HAVING COUNT (tbl1Assiduidade.Assiduidade FROM tbl1Assiduidade WHERE tbl1Assiduidade.Assiduidade = 2) > = 3"

Me.Requery


End Sub

Upvotes: 0

Views: 63

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

  • Select all records where tbl1Assiduidade.Assiduidade = 2
  • Group the records by Name & UFCD.

  • Choose the groups having 3 or more records.

    select t.Nome, t.UFCD 
from tbl1Assiduidade t
where t.Assiduidade = 2
group by t.Nome, t.UFCD 
having count(*) > 2

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

no need subquery you can try like below by using having filter

SELECT tbl1Assiduidade.Nome,
 tbl1Assiduidade.UFCD 
 FROM tbl1Assiduidade 
 bl1Assiduidade.Assiduidade = 2
group by tbl1Assiduidade.Nome,
 tbl1Assiduidade.UFCD
 having count(*)>=3

Upvotes: 0

Related Questions