Query or Report?

[seekinganswers]

I need to show who made honor roll. Seems simple right? So where do I put the criteria which is >="B" AND no "F". I've tried queries and report footers without success.

Layout as follows:
GradesTbl
ID
Name
Course
Teacher
Grade MP1

Each student has at least four grades, most have more. When I try using a report footer only the first grade is examined suggesting I need an array? Your suggestions would be appreciated.

 

[fredk]

The first thing that I would think you need to do is assign a number to each letter - I would create a table with two fields "grademp1" and "gradevalue"

I would then enter
A 1
B 2
C 3
D 4
etc

I would then creat a query using your gradestbl and the table that I would add, link it in the query by the grademp1 field. I would add teh gradevalue to the query and say <=2 .... I am a little confused about the &quot;F&quot; though - and also, don't you want to average all grades before you apply the criteria?

Fred

 

[seekinganswers]

Hi Fred,

Thanks for getting back to me.

I tried this earlier by updating all the letter grades to number grades, and didn't get the results I want. This data was downloaded from a Web site as comma quote delimited text and imported into Access, so it is what it is. A student can have anywhere from 4 to 6 records that each hold a grade, I need to have the report look at each grade for that student and only those that have all A's and B's qualify for Honor Roll. So, if a student has an 3 &quot;A's&quot; and 2 &quot;B's&quot; they qualify. If they have any other grade all of their grades should be excluded from the report and/or query.

 

[ChrisCalvert]

This may be a bit roundabout, but I think it may work. Just query all records with an 'F','D', or 'C'for the grade. Then run an UNMATCHED query between that, and all the records. This gives you a list of all grades by students that did not make a C,D, or F. Then run DISTINCT on that. And you have only the list of those students.

-Chris

 

[seekinganswers]

Hi Chris,

Thanks! That worked. Sorry I'm getting back to you so late. I got sidetracked.

Thanks again!