Multiple Classes

[Guest]

I have a table that contains all the classes for 3800 students. Each student
times 7 gets you around 23000 classes, each record contains:
SSN, LocalID, Name, Subject (ELA, Sci, SS, Math are the only ones I care
about), Class Level (1,2,3,4), Teacher Name, Period, and Course Name.

The problem is, some students will have 2 ELA or Sci classes due to
failures, illness, skipping etc. I need to know which is the higher course.

No problem, I made a select query, put in SSN, Subject (=ELA), Level (set to
Max), Teacher Name, and Period.

Nope, didn't work. I still had duplicates because as soon as I add teacher
name, the record is differnet. How do I narrow the table to show me only the
highest level of ELA class with the SSN, Subject, teacher name and period
along with it? Sorry for the midnight madness here.

 

[twoodmore via AccessMonster.com]

Select * from tblClasses as t1
where t1.ELA =
(Select Max(t2.ELA) from tblClasses as t2
where t1.SSN = t2.SSN)

 

[twoodmore via AccessMonster.com]

or actually;
Select * from tblClasses as t1
where t1.Level =
(Select Max(t2.Level) from tblClasses as t2
where t1.SSN = t2.SSN)

Select * from tblClasses as t1
where t1.ELA =
(Select Max(t2.ELA) from tblClasses as t2
where t1.SSN = t2.SSN)

I have a table that contains all the classes for 3800 students. Each student
times 7 gets you around 23000 classes, each record contains:

[quoted text clipped - 11 lines]

highest level of ELA class with the SSN, Subject, teacher name and period
along with it? Sorry for the midnight madness here.

 

[John Spencer]

Two query approach.
First get the max level, student, a subject in one query
SELECT SSN, Subject, Max([Class Level]) as MaxLevel
FROM YourTable
GROUP BY SSN, Subject

With that saved as qMaxLevel, you can join it to your table to return the
other information.

SELECT *
FROM YourTable INNER JOIN qMaxLevel
ON YourTable.SSN = qMaxLevel.SSN
AND YourTable.Subject = qMaxLevel.Subject
AND YourTable.[Class Level] = qMaxLevel.MaxLevel


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..