Simple Query, complicated user.

[James D.]

Background:

I have two tables; Student ; Courses.

The student table has the following fields:
Student ID
Name
Age
Grade
Address
Phone Number

The Courses table has the following fields:
RecordID
StudentID
StudenSemesterID
Course
Grade
Date

In this table it is common to have StudentID and SemesterID repeated for
each course(up to five). Potentially each student may have up to 5
records(lines per semester, not to mention past semesters(Different
StudentSemesterID).

StudentID SemesterID Course Grade Date
112 334 math a
112 334 computer a
112 334 sciense a

Goal:
There are three different courses I am interested in being math, science,
computers
The grade must be an A

I need to count the number of students who took either math AND science OR
math AND computers AND had a grade of an A.

I also need to count the number of StudentSemesters where they took either
math and science or math and computers and had a grade of an A.


I seriously can’t seem to figure this one out. Any suggestions would be
greatly appreciated.

All the best.

James D.

 

[Jeff L]

Your best bet would be to make multiple queries. Make one for Math
with grade A, Science with grade A, and Computers with grade A. Now
make a query that joins the Math query with the Science query on
Student ID. Your results will be all students that took both Math and
Science and received an A in both. Do the same with Math and
Computers.

You can do the same for your second problem, except this time join your
queries on SemesterID. The results should give you the number of
Semesters where students took Math and Science or Math and Computers
and received an A in both.

Hope that helps!

 

[Bill (Unique as my name)]

This query (query1) shows how many students took math and science and
got an A for a grade.

SELECT Students.Name, Courses.StudensemesterID, Courses.Course,
Courses.Grade, Courses_1.Course
FROM (Students INNER JOIN Courses AS Courses_1 ON Students.[Student ID]
= Courses_1.StudentID) INNER JOIN Courses ON Students.[Student ID] =
Courses.StudentID
GROUP BY Students.Name, Courses.StudensemesterID, Courses.Course,
Courses.Grade, Courses_1.Course
HAVING (((Courses.StudensemesterID)=334) AND ((Courses.Course)="math")
AND ((Courses.Grade)="A") AND ((Courses_1.Course)="Science"));

This query (query2) show how many students took math and computer and
got an A for a grade.

SELECT Students.Name, Courses.StudensemesterID, Courses.Course,
Courses.Grade, Courses_1.Course
FROM (Students INNER JOIN Courses ON Students.[Student ID] =
Courses.StudentID) INNER JOIN Courses AS Courses_1 ON Students.[Student
ID] = Courses_1.StudentID
GROUP BY Students.Name, Courses.StudensemesterID, Courses.Course,
Courses.Grade, Courses_1.Course
HAVING (((Courses.StudensemesterID)=334) AND ((Courses.Course)="math")
AND ((Courses.Grade)="A") AND ((Courses_1.Course)="Computer"));

This query (query3) shows the combined total of query1 and query2

SELECT [query1].Name, [query2].Name
FROM 1 INNER JOIN 2 ON [1].Name = [2].Name;
(you can adjust it to join on student ID)

This expression tallies the total from query3

Dcount("name", "query3")

At least, I think that's what's going on. (apologies to Marvin
Gaye)

Background:

I have two tables; Student ; Courses.

The student table has the following fields:
Student ID
Name

Snip

 

[Bill (Unique as my name)]

Or maybe your could use just one query

SELECT Students.Name, Courses.StudensemesterID, Courses.Course,
Courses.Grade, Courses_1.Course, Courses_2.Course
FROM ((Students INNER JOIN Courses AS Courses_1 ON Students.[Student
ID] = Courses_1.StudentID) INNER JOIN Courses ON Students.[Student ID]
= Courses.StudentID) INNER JOIN Courses AS Courses_2 ON
Students.[Student ID] = Courses_2.StudentID
GROUP BY Students.Name, Courses.StudensemesterID, Courses.Course,
Courses.Grade, Courses_1.Course, Courses_2.Course
HAVING (((Courses.StudensemesterID)=334) AND ((Courses.Course)="math")
AND ((Courses.Grade)="A") AND ((Courses_1.Course)="Science") AND
((Courses_2.Course)="Computer"));

and then set the dcount expression to student id.

Background:

I have two tables; Student ; Courses.

The student table has the following fields:
Student ID
Name
Age

Snip