Group By

[ArviLaanemets]

Create a saved query, p.e. qGradesTotal

qGradesTotal = 
SELECT test.Term, test.Last, test.First, SUM(Iif(test.Grade < "D",1,0))  AS Passed, SUM(Iif(test.Grade >= "D",1,0))  AS Missed
FROM TblHotWaterTest_2 test
GROUP BY test.Term, test.Last, test.First;

Now you can query


SELECT * FROM qGradesTotal WHERE Passed >= 3
or
SELECT * FROM qGradesTotal WHERE Passed < 3

[June7]

So count FA as failure and ignore the W?

Change the criteria to: [Grade] LIKE 'F*'

[Dean0_53]

Oh, Sorry about that. We do have a unique identifier call a PIDM. I added it with fake numbers. Each student has a PIDM different that a SSN number. Hope this helps.

[Dean0_53]

I have to sign off for now. Thanks again for your help!

[June7]

Well, use the PIDM in place of the [Last] & [First].

[Dean0_53]

How would I use PIDM instead of name? Thanks!

SELECT test.PIDM_KEY AS Pidm, test.LAST_NAME AS [Last], test.FIRST_NAME AS [First], test.SUBJ_CODE, test.CRSE_NUMBER, test.GRDE_CODE_FINAL AS Grade, Sum(IIf([test].[Grade]<"D",1,0)) AS Passed, Sum(IIf([test].[Grade]>="D",1,0)) AS Missed
FROM test
GROUP BY test.PIDM_KEY, test.LAST_NAME, test.FIRST_NAME, test.SUBJ_CODE, test.CRSE_NUMBER, test.GRDE_CODE_FINAL
ORDER BY test.LAST_NAME, test.FIRST_NAME;

[June7]

Well, using suggestion from post 14, suppose I meant like:

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

[Dean0_53]

Hello June 7,

You have been very helpful and we're getting very close. I do have a few more questions. Is there any way to get all the bad grades to sum? So if a student has anything below a F, (FA, W, I) they will all sum together? I have attached a new copy of the database. When I run the QryMissed, the total aren't quite correct. The missed grades total don't match up with the grade table. Bobby Brady doesn't have any missed grades.

Thanks again for all your help!

DeanO

[Dean0_53]

I won't let me add another file. Is it because there are other files already attached?

Thanks

[June7]

Should be able to add another file. Or if you can still edit your post, replace file.

[Dean0_53]

I zipped the file, but I don't know if it attached.
Grade Test.zip

[June7]

It attached. Don't see PIDM field anywhere.

The expression fails because D sorts as less than D-, C < C-, etc.

Why are you bothering with MAKE TABLE? Just use a query to produce the data. Modify Query1:

SELECT test.Term, test.Last, test.First, Sum(IIf([test].[Grade] Not In ("D-","F"),1,0)) AS Passed, Sum(IIf([test].[Grade] In ("D-","F"),1,0)) AS Missed
FROM TblHotWaterTest_2 AS test
GROUP BY test.Term, test.Last, test.First;