Query across Multiple Tables MAX()

[cbrashear]

I am new to sql and have not been able to resolve this issue. I have a query that shows me the information on students we serve. The query pulls all of their classes and the status results as well as contact information. I need to query down to the LAST class taken and the students status. If I take out the following fields I get the correct return but I have to have all the information dbo.Class.Year, dbo.ClassResult .ClassResultSta tusID, dbo.Class.Semes terID, dbo.Location.Lo cationName, dbo.Institution .InstName

SELECT MAX(dbo.ClassRe sult.ClassID)AS LASTCLASS,dbo.P erson.PersonID, dbo.Person.Last Name, dbo.Person.Firs tName,dbo.Addre ss.Address1,dbo .Address.City,d bo.Address.Stat e, dbo.Address.Zip ,dbo.Class.Year ,dbo.ClassResul t.ClassResultSt atusID, dbo.Class.Semes terID,dbo.Locat ion.LocationNam e, dbo.Institution .InstName

FROM dbo.Person INNER JOIN dbo.Person_Addr ess ON dbo.Person.Pers onID = dbo.Person_Addr ess.PersonID INNER JOIN dbo.Address ON dbo.Person_Addr ess.AddressID = dbo.Address.Add ressID INNER JOIN dbo.ClassResult ON dbo.Person.Pers onID = dbo.ClassResult .PersonID INNER JOIN dbo.Class ON dbo.ClassResult .ClassID = dbo.Class.Class ID INNER JOIN dbo.V_ClassResu ltStatusID ON dbo.ClassResult .ClassResultSta tusID = dbo.ClassResult .ClassResultSta tusID INNER JOIN dbo.V_SemesterI D ON dbo.Class.Semes terID = dbo.Class.Semes terID INNER JOIN dbo.Location ON dbo.Class.Locat ionID = dbo.Location.Lo cationID INNER JOIN dbo.Institution ON dbo.Person.Inst itutionID = dbo.Institution .InstitutionID

Group by dbo.Person.Pers onID,dbo.Person .LastName,dbo.P erson.FirstName ,dbo.Address.Ad dress1,dbo.Addr ess.City, dbo.Address.Sta te,dbo.Address. Zip,dbo.Class.S emesterID, dbo.Class.Year, dbo.ClassResult .ClassResultSta tusID, dbo.Location.Lo cationName,dbo. Institution.Ins tName

Having (dbo.Class.Year = dbo.Class.Year and dbo.Class.Semes terID = Class.SemesterI D)and count()> 1 and dbo.Location.Lo cationName = dbo.Location.Lo cationName and count()> 1 and dbo.Institution .InstName = dbo.Institution .InstName and count()> 1 and ((NOT (dbo.ClassResul t.ClassResultSt atusID = 8)) and count()> 1 OR (NOT (dbo.ClassResul t.ClassResultSt atusID = 10)) and count()> 1 OR (NOT (dbo.ClassResul t.ClassResultSt atusID = 13))and count()> 1 OR (NOT (dbo.ClassResul t.ClassResultSt atusID = 12)) and count()> 1OR (NOT (dbo.ClassResul t.ClassResultSt atusID = 4))and count()> 1)

ORDER BY dbo.Person.Last Name, dbo.Person.Firs tName

[cbrashear]

Have cleaned it up
SELECT MAX(ClassResult .ClassID) AS LASTCLASS,
Person.PersonID ,
Person.LastName ,
Person.FirstNam e,
Address.Address 1,
Address.City,
Address.State,
Address.Zip,
Class.Year,
ClassResult.Cla ssResultStatusI D
FROM Person

INNER JOIN Person_Address ON Person.PersonID = Person_Address. PersonID
INNER JOIN Address ON Person_Address. AddressID = Address.Address ID
INNER JOIN ClassResult ON Person.PersonID = ClassResult.Per sonID
INNER JOIN Class ON ClassResult.Cla ssID = Class.ClassID

GROUP BY Person.PersonID ,
Person.LastName ,
Person.FirstNam e,
Address.Address 1,
Address.City,
Address.State,
Address.Zip,
Class.Year,
ClassResult.Cla ssResultStatusI D
HAVING (ClassResult.Cl assResultStatus ID NOT IN (4, 8, 10, 12, 13))
ORDER BY Person.LastName , Person.FirstNam e