Multiple Classes

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Select * from tblClasses as t1
where t1.ELA =
(Select Max(t2.ELA) from tblClasses as t2
where t1.SSN = t2.SSN)
 
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:
Click to expand...
[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.
Click to expand...
Click to expand...
 
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
..
 
Back
Top