Okay, if anyone can help me out with this please do. I have a table with school-related stuff in it and I would like to spit out a list of those students who made all A's. Unfortunately the table setup is kinda screwy. Each row (briefly) contains fields last_name, first_name, class_name, and final_grade. So the table might look like:

Brown Brian Geometry 98
Smith Kevin English 76
Jones James Geometry 91
Brown Brian Spanish 88
Jones James Spanish 99
etc...

So you can see it kinda sucks. I think there's some way to do it by doing one query that returns all the unique names then doing a query on that to return the grades for each name then querying (again) to return the names with all A's. Quite complicated, and I can't quite work it out. If anyone knows an easier way, please help, or explain this to me better. Thanks so much!

You're right, the table structure is extremely screwy. No chance of fixing it, is there?
What a fascinating problem. See below for a more elegant solution to the problem.

[This message has been edited by David R (edited 05-29-2002).]

No, not screwy at all. The grade depends on both person and class. The worst part is that the person should be in a separate table and you should have a single field that is a student number. Be that as it may, the table is usable as long as nobody's name is spelled differently in two different rows but is still supposed to be the same person. (That's why you should have person ID and link to a separate table...)

Anyway, you need to ask a couple of questions first.

1. Does the same exact grading curve apply to all classes? I'm going to assume YES on this one, and an A is 90 or above.

2. Is there a "minimum number of classes to qualify" requirement? I'm guessing NO on that one.

In the query grid view, make sure the Sigma button is clicked IN.

Use the query grid to select student name and grade. Or include grade twice if you wish, once for count and once for average.

Under the last-name column do a group-by. Under the first-name column do a group-by.
Under the grade column select Average. Put a label of "AvgGrade:" in front of the Grade in the field name box.
Under the second grade column select Count. Put a label of "ClassCount:" in front of the second Grade in the field name box.

Now you can write a query to select [Last Name], [First Name], [AvgGrade], [ClassCount] from MyFunkyQuery where [AvgGrade] > 89 ;

I agree totally with Doc that the Primary Key should be an ID number so that you don't get two Bob Jones who are actually two people... ya know?

But, oh so simply, I would create qryA which includes Name and Grade with a filter of Grade > your highest B score range. Then, qryLowerThanA with the Name and Grade with a filter of < your lowest A score range. Then, simply do a query using the Query Wizard and find Unmatched records. All Students appearing on qryA which do not show on qryLowerThanA should, by all reasoning, have all A's.

Ta... Dah?
Wink

And, BTW, the only reason I dare propose this is because I believe that Doc's solution would only return records of students with an average grade of A... not every grade being an A. Suppose someone had a 98 (A) and an 89 (B). Certainly the average grade would be A, but he did not make straight A's. (Sorry)
Tess

[This message has been edited by TessB (edited 05-29-2002).]

Aha, Tess' idea gives me an even simpler solution:
A Totals Query that Groups By (Name/ID) and gives the Min value of [Grade]. By deduction if a student's lowest grade is above whatever your Criteria is ( >=90? ), then they should be on your list. One query.

And by "screwy" I simply meant that the student data should not be in the Grades table, as you pointed out Doc_Man. If you can reduce the name fields to an ID number then only two fields are required.

[This message has been edited by David R (edited 05-29-2002).]

Thanks so much. You've helped a great deal. One query, just like the last post said. Great.

Bravo team! I love this place.