Grading AVG Query Confusion

[pyrotaz]

Playing around with Access and found this question in a book I picked up at the library and was attempting to figure it out. Just would like some one who knows more about access to take a look and point me in the right direction.


Here the question:
There is a table (called Grades) that includes course, firstName, lastName, lastAccessDate, and all of your grades for each assignment and exam (a1, a2, a3, a4, a5, a6, a7, m1, m2, final). Assume that each of the classes has exactly the same distribution of assignments, namely that there are seven assignments, two midterms, and a final.


Write a query that will determine the number of students who have averaged at least an 83 on the two exams and have not yet submitted the final in each of the courses, sorted by the course ascending

Here are two queries I have tried and get errors are either of them close to being right?
A:
SELECT AVG (m1+m2)=m3
FROM Grades
Where m3 >= "83"


;
Or
B:
SELECT AVG(m1+m2)
From grades
Where AVG >= "83"
ORDER BY course ASC,

[ranman256]

query 'A' seem correct. Thats the test avg part.
now make qry B to pull Students who have NOT submitted a final.

im not sure how your data is, but you need either:
students with null grade for final
or
(you need an outer join) using tGrades and tStudents to see who did Not

once you have qA and qB, then join the 2 together (as qC)

[aytee111]

Simplify your thinking, create multiple queries and start from the beginning.
- list of students and with two exams and no final
etc
Write down each step and then turn them into queries.

What does "average 83" mean, is that a number on the table or is it an average of a number of scores?

[pyrotaz]

Thanks you for your help and suggestions. aytee111 I am working on the first step you recommended and this is what have completed so far. I am able to get the no final to work but the two exams are stumping me. This is what I have so far:

SELECT grades.firstName, grades.lastName
FROM grades
WHERE m1 Or m2 is Not NULL AND final = "Null"
;

[aytee111]

Write it in English first - final is null (is this right? not zero?) AND there is an m1 and/or an m2. Then let Access create the SQL for you using the query design window, unless you are comfortable writing SQL. Then add the criteria - first row will be final null and m1 not null, second line (OR) will be final null and m2 not null.

[pyrotaz]

Here is a what I have for a table:



This is the first time I've really used access, trying to teach myself. Thank you for having patience with me. I have a little experience with SQL but consider myself a beginner.
This is my latest attempt which returns nothing.

SELECT grades.firstName, grades.lastName
FROM grades
WHERE final=Null And m1=Not Null
Or final=Null and m2 =Not NULL
;

[Micron]

I don't think you can calculate the average across fields using the average function, with or without making it a Totals query as per first post.
WHERE m1 Or m2 is Not NULL You can group things like this in your mind and know what you want, but if you really think about it, what you wrote isn't what you want when it comes to computer logic. The pc will ask "Where m1 is greater than or is less than or equals, etc what? What's going on with m1? I get the Or part - they're saying Or m2 is Not Null. But I don't get the m1 part." For your case, it isn't OR anyway - it's AND. OR means that one can be Null as long as the other isn't. What I didn't do is deal with the possibility that m1 or m2 could be Null in the expression, since the criteria should take care of nulls.

SELECT tblGrades.Course, tblGrades.[m1], tblGrades.[m2], (([m1]+[m2])/2) AS Expr1 FROM tblGrades
WHERE (((tblGrades.[m1]) Is Not Null) AND ((tblGrades.[m2]) Is Not Null) AND ((([m1]+[m2])/2)>=83) AND ((tblGrades.f) Is Null))
ORDER BY tblGrades.Course;

This also doesn't provide a count - I'm not sure what you intended by that statement in the beginning.
EDIT: I treated m fields as numbers - just remembered there are quotes around 83 in previous posts for some reason.

[pyrotaz]

Micron thank you for your response. You are right I was trying to write it the way I was thinking. Now that I'm looking at it it makes total sense! Thanks again!!

[Micron]

You're welcome. But did what I wrote work for you (allowing for different table and/or field names)?
This Not NULL won't work. Is Null or Is Not Null is for sql. IsNull is for vba. They are not interchangeable either.