getting top result for only some records in a query

V

vanguardaccess

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
 
K

KARL DEWEY

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"));
 
K

Ken Sheridan

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
 
K

Ken Sheridan

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
 
V

vanguardaccess

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?
 
K

Ken Sheridan

I'm having some difficulty seeing the wood for the trees. Which column from
which table contains the 'M', 'S' and 'H' values (or their real life
equivalents if they are dummy values), and which column from which table
contains the scores?

Ken Sheridan
Stafford, England

vanguardaccess said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selecting top 2 records on same row 4
PR_RTF_COMPRESSED open failed 0
This error keeps coming back 5
Need help on this one 2
create a form for searching information 3
Windows Xp Question 2
Windows XP Question 1
subquery to return max date misses some records altogether? 2

Top