Group By

[Dean0_53]

Hello,




I am brand new to forum, but have been writing simple Access quires for many years. My population is a group of students with grades. I want to be able to only select those students that have 3 or more passing grades. So for example if a student has 3 passing grades A, B or C and two failing grades how can I select only those students with 3 or or more passing grades and if there are many students in this group, how can I only get those with 3 or more?


Make sense?


Thanks for your help!

Deano

[orange]

Show readers your table designs.
What have you tried?

[June7]

Do you also want the students with 3 or more passing grades even if they have failing grades?

[Dean0_53]

Sure, as long as I can make sure they have at least three failing or three passing grades.

Thanks

[June7]

Now I am really confused - all students with 3 passing OR 3 failing? Maybe you better show an example of raw data and output you want.

[Dean0_53]

Why don’t we just say, if a student has 5 courses and passed at least thee of them. I want to know any student that passed basically with a grade do D or higher in at least three courses.

Make sense?

[June7]

Consider:

SELECT StudentGrades.Student, Sum(IIf([Grade]<="d",1,0)) AS PassCount
FROM StudentGrades
GROUP BY StudentGrades.Student
HAVING (((Sum(IIf([Grade]<="d",1,0)))>=3));

[Dean0_53]

As I think about this some more, I think what I would like to be able show all the students grades, but figure out a way to desperate out students that have at least 3 of the bad grades mentioned above. So find a way to group these students that have a least 3 bad grades, but also show any passing grade.

Make sense?

Thanks!

[Dean0_53]

Here is a sample of the database.

SELECT TblHotWaterTest_2.Term, TblHotWaterTest_2.Last, TblHotWaterTest_2.First, TblHotWaterTest_2.Subj, TblHotWaterTest_2.Crse, TblHotWaterTest_2.Grade
FROM TblHotWaterTest_2
ORDER BY TblHotWaterTest_2.Last, TblHotWaterTest_2.First;

I need to change something to get these to group those students with at least three bad grades, but still show all there grades. If possible.
Thanks,

[June7]

There is no db attached.

Started out wanting to show students with 3 passing grades and now the criteria is 3 bad grades???

Is there a StudentID field in table?

SELECT * FROM TblHotWaterTest_2 ORDER BY DCount("*", "TblHotWaterTest_2", "StudentID=" & [StudentID] & " AND Grade='F'"), StudentID;

[Dean0_53]

Sorry it has taken so long to post this. Again, if possible, for this population, I only want those students that have at least 3 bad grades, but also include all of their grades. So one test student, Bob Smith has 3 bad grades but also one passing grade. We need to separate students with at least three bad grades. Bill Kane also has a good grade but at least 3 bad grades.

How to do I attach a database?

Thanks!

[June7]

Did you try any of the suggestions?

Follow instructions at bottom of my post.

[Dean0_53]

Grade Test.accdbI did, but i haven't had too much luck.

[June7]

What do FA and W grades mean? Are they failing? Bob Smith has 4 F, 1 FA, 1 W, 1 C+; Bill Kane has 3 F, 1 W, 1 B. The following queries return those 12 records as no one else has 3 or more F.

Revision of my last suggestion:

SELECT TblHotWaterTest_2.*, DCount("*","TblHotWaterTest_2","[Last] & [First]='" & [Last] & [First] & "' AND Grade='F'") AS CountFail
FROM TblHotWaterTest_2
WHERE (((DCount("*","TblHotWaterTest_2","[Last] & [First]='" & [Last] & [First] & "' AND Grade='F'"))>=3))
ORDER BY TblHotWaterTest_2.[Last] & [First];

or

SELECT TblHotWaterTest_2.*
FROM TblHotWaterTest_2
WHERE ((([Last] & [First]) In (SELECT [LAST] & [First] FROM TblHotWaterTest_2 GROUP BY [Last] & [First] HAVING Sum(IIf([Grade]="F",1,0))>=3)))
ORDER BY [Last] & [First];

Names make very poor unique identifiers.

[Dean0_53]

Yes W-Withdrawn and FA-Failure lack of attendance. Basically and grade lower then D-.

Thanks,