getting top result for only some records in a query
in [General]
|
Prev: OpenReport using macro
Next: Requery in Access 2000
From: vanguardaccess on 1 Jul 2008 12:16 I have a query with the following fields and sample data Id-----Exam-----Score 01-----M-----70 01-----S-----65 01-----H-----90 01-----H-----95 01-----M-----72 01-----M-----97 02-----M-----65 02-----H-----79 02-----H-----81 02-----S-----81 02-----S-----72 I would like the query to only show the top result from the M and S exam, but all of the H exams. How can I do this? --vanguardaccessman
From: KARL DEWEY on 1 Jul 2008 13:33 Try this --- SELECT vanguardaccess.ID, vanguardaccess.Exam, Score FROM vanguardaccess WHERE (vanguardaccess.Exam)="H" UNION ALL SELECT vanguardaccess.ID, vanguardaccess.Exam, Max(vanguardaccess.Score) AS MaxOfScore FROM vanguardaccess GROUP BY vanguardaccess.ID, vanguardaccess.Exam HAVING (((vanguardaccess.Exam)="M" Or (vanguardaccess.Exam)="S")); -- KARL DEWEY Build a little - Test a little "vanguardaccess" wrote: > I have a query with the following fields and sample data > > Id-----Exam-----Score > 01-----M-----70 > 01-----S-----65 > 01-----H-----90 > 01-----H-----95 > 01-----M-----72 > 01-----M-----97 > 02-----M-----65 > 02-----H-----79 > 02-----H-----81 > 02-----S-----81 > 02-----S-----72 > > I would like the query to only show the top result from the M and S exam, > but all of the H exams. How can I do this? > > --vanguardaccessman
From: Ken Sheridan on 1 Jul 2008 13:59 If you don't need the Id values in the result set you can use a UNION ALL operation: SELECT Exam, MAX(Score) As TopScore FROM ResultsTable WHERE Exam IN ("M","S") GROUP BY Exam UNION ALL SELECT Exam, Score FROM ResultsTable WHERE Exam= "H"; If you need to include the Id column also in the result set then use a subquery in the WHERE clause: SELECT * FROM ResultsTable As RT1 WHERE (Exam IN("M","S") AND Score = (SELECT MAX(Score) FROM ResultsTable AS RT2 WHERE RT2.Exam = RT1.Exam)) OR Exam = "H"; Ken Sheridan Stafford, England "vanguardaccess" wrote: > I have a query with the following fields and sample data > > Id-----Exam-----Score > 01-----M-----70 > 01-----S-----65 > 01-----H-----90 > 01-----H-----95 > 01-----M-----72 > 01-----M-----97 > 02-----M-----65 > 02-----H-----79 > 02-----H-----81 > 02-----S-----81 > 02-----S-----72 > > I would like the query to only show the top result from the M and S exam, > but all of the H exams. How can I do this? > > --vanguardaccessman
From: Ken Sheridan on 1 Jul 2008 14:08 You'll notice that Karl's solution and mine differ. His assumes top result per Id/Exam, mine assumes top result per Exam. So take your choice depending on which it is you want. Ken Sheridan Stafford, England
From: vanguardaccess on 1 Jul 2008 23:33
Thanks, I really appreciate the help. I posted a simplified example of the problem I am trying to solve, figuring that if I can get the concept I can apply it to my query. Unfortunately, I have been unsuccessful. To show you what I am up against, this is my SQL: SELECT tbl_bios.VANGUARD_ENTRY_DATE, tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID, tbl_allstudentsallgrades_hsst.LastName, tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year, tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course, DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course] & "'") AS DEPT, tbl_department_requiredcredits.Required_Credits, tbl_bios.DIPLOMATYPE, tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark, tbl_allstudentsallgrades_hsst.Mark_Text, tbl_allstudentsallgrades_hsst.Credits, DetermineResult([Mark]) AS Vangrade, [FirstName] & " " & [LastName] AS FullName, tbl_bios.ADVISOR, tbl_bios.DOB, tbl_bios.SEX, tbl_bios.ADDRESS, tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP, tbl_bios.GRADE, tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY] & ", " & [STATE] & " " & [ZIP] AS FnlAddress, [PARENT1] & ", " & [PHONE1] AS FullContact, tbl_bios.COHORT, qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA, tbl_bios.SPEDSTS, tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE, tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL, tbl_allstudentsallgrades_hsst.School FROM tbl_bios INNER JOIN ((qry_finalGPA INNER JOIN tbl_allstudentsallgrades_hsst ON qry_finalGPA.StudentID = tbl_allstudentsallgrades_hsst.StudentID) INNER JOIN (tbl_coursecodes_departments INNER JOIN tbl_department_requiredcredits ON tbl_coursecodes_departments.Department = tbl_department_requiredcredits.DepartmentList) ON tbl_allstudentsallgrades_hsst.Course = tbl_coursecodes_departments.CourseCodes) ON (tbl_bios.OSIS = tbl_allstudentsallgrades_hsst.StudentID) AND (tbl_bios.DIPLOMATYPE = tbl_department_requiredcredits.DiplomaType) GROUP BY tbl_bios.VANGUARD_ENTRY_DATE, tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID, tbl_allstudentsallgrades_hsst.LastName, tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year, tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course, DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course] & "'"), tbl_department_requiredcredits.Required_Credits, tbl_bios.DIPLOMATYPE, tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark, tbl_allstudentsallgrades_hsst.Mark_Text, tbl_allstudentsallgrades_hsst.Credits, [FirstName] & " " & [LastName], tbl_bios.ADVISOR, tbl_bios.DOB, tbl_bios.SEX, tbl_bios.ADDRESS, tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP, tbl_bios.GRADE, tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY] & ", " & [STATE] & " " & [ZIP], [PARENT1] & ", " & [PHONE1], tbl_bios.COHORT, qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA, tbl_bios.SPEDSTS, tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE, tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL, tbl_allstudentsallgrades_hsst.School HAVING (((tbl_allstudentsallgrades_hsst.LastName) Like [Enter a Last Name] & "*") AND ((tbl_bios.ADVISOR) Like [What Advisor Are You Looking For?] & "*") AND ((tbl_bios.GRADE) Like [What Grade Are You Looking For?] & "*") AND ((tbl_bios.COHORT) Like [What Cohort Are You Looking For?] & "*")); Karl's response was what I need - in that I need the top exam score for each ID (sorry for being vague when I originally posted the question). Can you please explain how I can embed the code into my SQL? Is there a way to do this in the traditional design view? -- --vanguardaccessman "Ken Sheridan" wrote: > You'll notice that Karl's solution and mine differ. His assumes top result > per Id/Exam, mine assumes top result per Exam. So take your choice depending > on which it is you want. > > Ken Sheridan > Stafford, England > > |