Query with conditional statement

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.

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

"gremillion1" wrote:

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.

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.

Golfinray wrote:
Under your student grades field in the criteria put 73 or 70 or whatever
you need.

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.

On Tue, 14 Oct 2008 17:11:31 GMT, "gremillion1" u46912@uwe wrote:

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?
--

John W. Vinson [MVP]

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.


John W. Vinson wrote:
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?

On Tue, 14 Oct 2008 20:06:13 GMT, "gremillion1" u46912@uwe wrote:

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.

John W. Vinson wrote:
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 [MVP]

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200810/1

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200810/1

On Thu, 16 Oct 2008 13:45:48 GMT, "gremillion1 via AccessMonster.com"
u46912@uwe wrote:

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.
--

John W. Vinson [MVP]