Query with conditional statement

[gremillion1]

I am an access novice. . . .

I have a query with an expression to find the average of two grades per
subject (5 subjects). That works fine. However, what I need is for the
query to display those students name who has ANY average below 73 AND only
those averages that meet the conditional statement. Also, I don't want any
averages above 73 to be displayed.

 

[Golfinray]

Under your student grades field in the criteria put <73 or > 70 or whatever
you need.

 

[gremillion1]

Ok. I did that, but even if the conditional statement is satisfied it still
shows all subject averages for the student even if the average is above 73.
I only want to show subject averages that are under 73.

I am an access novice. . . .

[quoted text clipped - 3 lines]

those averages that meet the conditional statement. Also, I don't want any
averages above 73 to be displayed.

 

[John W. Vinson]

I am an access novice. . . .

I have a query with an expression to find the average of two grades per
subject (5 subjects). That works fine. However, what I need is for the
query to display those students name who has ANY average below 73 AND only
those averages that meet the conditional statement. Also, I don't want any
averages above 73 to be displayed.

Please post the structure of your table and a couple of rows of sample data,
and an example of the desired output. Do you have one grade per FIELD (bad) or
one grade per record?

 

[gremillion1]

Actually, I figured that problem out by placing the following conditional
statement in the field box of query (1 for each subject):
SC_Avg: IIf((([PI-2]![SC_1st]+[PI-2]![SC_2nd])/2)<=73,(([PI-2]![SC_1st]+[PI-2]
![SC_2nd])/2)," ")

Now on my form I need a button that generates a list of students with 2 or
more averages that fit that are below 73.

I am an access novice. . . .

[quoted text clipped - 3 lines]

those averages that meet the conditional statement. Also, I don't want any
averages above 73 to be displayed.

Please post the structure of your table and a couple of rows of sample data,
and an example of the desired output. Do you have one grade per FIELD (bad) or
one grade per record?

 

[John W. Vinson]

Actually, I figured that problem out by placing the following conditional
statement in the field box of query (1 for each subject):
SC_Avg: IIf((([PI-2]![SC_1st]+[PI-2]![SC_2nd])/2)<=73,(([PI-2]![SC_1st]+[PI-2]
![SC_2nd])/2)," ")

Now on my form I need a button that generates a list of students with 2 or
more averages that fit that are below 73.

Shrug. My request stands. I have no way to help you write that query since you
have chosen not to post any usable information about your table structure.

I am an access novice. . . .

[quoted text clipped - 3 lines]

those averages that meet the conditional statement. Also, I don't want any
averages above 73 to be displayed.

Please post the structure of your table and a couple of rows of sample data,
and an example of the desired output. Do you have one grade per FIELD (bad) or
one grade per record?

 

[gremillion1 via AccessMonster.com]

Table example:

Last First Read-1 Lang-1 Read -2 Lang-2
Doe Jane 89 90 88 50
Smith Joe 50 60 70 80
Cook Willie 100 89 90 87

The desired output is:
Last First Read-Avg Lang-Avg
Smith Joe 60 70

I only want to display students with 2 or more averages below 73. This is
why Jane Smith doesn’t appear (only one below 73), and Willie Cook doesn’t
appear (none below 73.)

Currently I’m getting:
Last First Name Read Avg Lang-Avg
Doe Jane 70
Smith Joe 60 70
Cook Willie

 

[gremillion1 via AccessMonster.com]

This one is formatted better!

Table example:

Last First Read-1 Lang-1 Read -2 Lang-2

Doe Jane 89 90 88
50
Smith Joe 50 60 70
80
Cook Willie 100 80 90
87

The desired output is:
Last First Read-Avg Lang-Avg
Smith Joe 60 70

I only want to display students with 2 or more averages below 73. This is
why Jane Smith doesn’t appear (only one below 73), and Willie Cook doesn’t
appear (none below 73.)

Currently I’m getting:
Last First Name Read Avg Lang-Avg
Doe Jane 70
Smith Joe 60 70
Cook Willie

 

[John W. Vinson]

Table example:

Last First Read-1 Lang-1 Read -2 Lang-2
Doe Jane 89 90 88 50
Smith Joe 50 60 70 80
Cook Willie 100 89 90 87

So... what will you do when you add a new course or test? Restructure your
table, redesign all your queries, redesign all your forms, redesign all your
reports? OUCH!

You're "committing spreadsheet", storing data (e.g. Read-1) in fieldnames.

If each student can take multiple tests/courses, and each course can be taken
by multiple students, a proper design would be:

Students
StudentID <Primary Key>
LastName
FirstName
<other biographical data>

Courses
CourseID <Primary Key>
CourseName
Category (e.g. Read, Lang, Maths, etc.)
<info about the course>

Scores
StudentID <link to Students, who took the course>
CourseID <what course they took>
Score <how well they did>

This design will make it very easy to flexibly calculate averages across
students, across courses, across a subset of courses, etc.

The desired output is:
Last First Read-Avg Lang-Avg
Smith Joe 60 70

I only want to display students with 2 or more averages below 73. This is
why Jane Smith doesn’t appear (only one below 73), and Willie Cook doesn’t
appear (none below 73.)

Very difficult with your current non-normalized table design.