SELECT MAX(value) from table1 and add several associated values from table2

[Zoran]

Hi guys,

I have an assignment for which I use MS Access 2007. I have 2 tables
Student and Unit Enrolment.
The table Student holds info on students (StudentID, first name, last
name, phone etc)
The table UnitEnrol holds info on student enrolments in units
(StudentID, UnitCode, UnitGrade etc).

I am trying to get a report on a student who scored the highest grade
in a specific unit.
It works okay if I select just the score:

SELECT MAX (UnitGrade)
FROM UnitEnrol
WHERE UnitCode = "ICT100"

but when try to add several fields from another table, it returns
multiple records:

SELECT MAX (UnitGrade), SfName, SlName, PhoneNo
FROM Student, UnitEnrol
WHERE UnitCode = "ICT100" and Student.StudentID = UnitEnrol.StudentID;
GROUP BY SfName, SlName, PhoneNo;

it returns all records for the specified unit.

I also tried several combinations with HAVING and subqueries, but it
did not work.

Any advice would be highly appreciated.

Thanks

 

[Zoran]

This one returns all values for the specified unit. For some reason it
ignores MAX function.

SELECT MAX(UnitGrade), SfName, SlName, PhoneNo, UnitCode
FROM Student INNER JOIN UnitEnrol ON Student.StudentID =
UnitEnrol.StudentID
GROUP BY UnitGrade, SfName, SlName, PhoneNo, UnitCode
HAVING UnitCode = "ICT100";

 

[John W. Vinson]

Hi guys,

I have an assignment for which I use MS Access 2007. I have 2 tables
Student and Unit Enrolment.
The table Student holds info on students (StudentID, first name, last
name, phone etc)
The table UnitEnrol holds info on student enrolments in units
(StudentID, UnitCode, UnitGrade etc).

I am trying to get a report on a student who scored the highest grade
in a specific unit.
It works okay if I select just the score:

SELECT MAX (UnitGrade)
FROM UnitEnrol
WHERE UnitCode = "ICT100"

but when try to add several fields from another table, it returns
multiple records:

SELECT MAX (UnitGrade), SfName, SlName, PhoneNo
FROM Student, UnitEnrol
WHERE UnitCode = "ICT100" and Student.StudentID = UnitEnrol.StudentID;
GROUP BY SfName, SlName, PhoneNo;

it returns all records for the specified unit.

I also tried several combinations with HAVING and subqueries, but it
did not work.

You don't have a Join. The clause

FROM Student, UnitEnrol

means to combind every record in the Student table... with every record in the
UnitEnrol table.

Try using a JOIN clause rather than putting the jion in the WHERE clause:

SELECT MAX (UnitGrade), SfName, SlName, PhoneNo
FROM Student INNER JOIN UnitEnrol
ON Student.StudentID = UnitEnrol.StudentID
WHERE UnitCode = "ICT100"
GROUP BY SfName, SlName, PhoneNo;

I have no idea what's in your table or how they're related so this may not
work as you expect, depending on which table is the "one" and which is the
"many".


--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John W. Vinson]

This one returns all values for the specified unit. For some reason it
ignores MAX function.

SELECT MAX(UnitGrade), SfName, SlName, PhoneNo, UnitCode
FROM Student INNER JOIN UnitEnrol ON Student.StudentID =
UnitEnrol.StudentID
GROUP BY UnitGrade, SfName, SlName, PhoneNo, UnitCode
HAVING UnitCode = "ICT100";

Sorry, missed this post before responding to your previous. How are the tables
related? What is the Primary Key of each table? If you don't GROUP BY, what
records do you see?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Zoran]

Hi John and thanks for your help,

PK of Student table is StudentID and PKs of UnitEnrol are StudentID,
UnitCode, StudyPeriod and StudyYear
The tables are connected 1(Student) to M(UnitEnrol) using StudentID
key and referential integrity is enforced.

If I try

SELECT MAX(UnitGrade), SfName, SlName, PhoneNo, UnitCode
FROM Student INNER JOIN UnitEnrol ON Student.StudentID =
UnitEnrol.StudentID
HAVING UnitCode = "CIS211";

a message pops up saying "You tried to execute a query that does not
include the specified expession "'SfName' as part of an aggregate
function"

I tried running this query

SELECT UnitGrade AS "Highest Result", SfName, SlName, PhoneNo,
UnitCode
FROM Student, UnitEnrol
WHERE UnitGrade = (SELECT MAX(UnitGrade) FROM UnitEnrol HAVING
UnitCode = "ICT100") and Student.StudentID = UnitEnrol.StudentID;

but it returns 2 records for 2 different users for 2 different units
having the same score. The common for these 2 users is that they both
enrolled into ICT100, but they scored different points. So the query
returns something like:

81 User1 ICT105
81 User7 ICT100

The thing is that the user 1 also enrolled in ICT100 but scored just
77.

This does not look good.

 

[Zoran]

This one also returns 2 records

SELECT UnitGrade AS "Highest Result", SfName, SlName, PhoneNo,
UnitCode

FROM Student INNER JOIN UnitEnrol ON Student.StudentID =
UnitEnrol.StudentID

WHERE UnitGrade = (SELECT MAX(UnitGrade) FROM UnitEnrol HAVING
UnitCode = "ICT100");

 

[John Spencer]

The following query should give you the students with the top grade for every
UnitCode. You can add a where clause to filter it down to any specific unit.
It will return more than one record per unit where individuals have tied for
the top grade.

SELECT Student.SfName
, Student.SlName
, Student.PhoneNo
, UnitEnrol.UnitCode
, UnitEnrol.UnitGrade
FROM (Student INNER JOIN UnitEnrol
ON Student.StudentID = UnitEnrol.StudentID)
INNER JOIN
(SELECT Max(UnitGrade) as TopGrade, UnitCode
FROM UnitEnrol
GROUP BY UnitCode) as TopGrades
ON UnitEntrol.UnitCode = TopGrades.UnitCode
AND UnitEnrol.UnitGrade = TopGrades.TopGrade


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I'd suggest a correlated subquery; first a query just on the UnitEnrol table
to find the highest score for each student for each class, and join THAT to
the Student table to find the additional student information:

SELECT SfName, SlName, PhoneNo, "ICT100" AS UnitCode,
(SELECT MAX(UnitGrade) FROM UnitEnrol WHERE
UnitCode = "ICT100" AND StudentID = Student.StudentID
GROUP BY StudentID, UnitCode) AS MaxScore
FROM Student;

Note that you can apply criteria in a Totals query using either the HAVING or
WHERE keywords. WHERE is applied before the totalling is done, limiting which
records get examined; HAVING is applied after, and is best reserved for
criteria on sums, counts, or the like, rather than on native table fields. The
query grid seems to generate HAVING clauses unless you choose the "where"
option on the Totals row.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Zoran]

Thanks guys for your help, it's really appreciated.

John Spencer, I tried your query and got a message "Syntax error in
JOIN operation". I see that the first query lists everything from the
2 tables for each students and that works. The second query is
supposed to create a new table that holds all the highest grades for
each unit and makes sure there are no repeats, but it does not work
when I execute it for itself.

In another attempt, Access actually complained when I used AND after
ON in a JOIN operation. I am not sure if this is allowed.

If I try this

SELECT SfName, UnitGrade
FROM Student INNER JOIN UnitEnrol ON Student.StudentID =
UnitEnrol.StudentID
WHERE UnitCode = "ICT100";

it works

but if I try this

SELECT SfName, UnitGrade
FROM Student INNER JOIN UnitEnrol ON Student.StudentID =
UnitEnrol.StudentID AND UnitCode = "ICT100";

it says "JOIN expression not supported".



John W. Vinson, your example actually lists details for all students,
whithout repeats, and sets ICT100 in the UnitCode fields for all of
them. It creates a new column MaxScore, but there are no values.


Now, I did some "tweaking" on my data by changing the score of the
unit ICT105 for the student user1 from 81 to 74. Now when I execute

SELECT UnitGrade AS "Highest Result", SfName, SlName, PhoneNo,
UnitCode
FROM Student, UnitEnrol
WHERE UnitGrade = (SELECT MAX(UnitGrade) FROM UnitEnrol HAVING
UnitCode
= "ICT100") and Student.StudentID = UnitEnrol.StudentID;

I get just one record

81 User7 ICT100

which is what I needed.

Since this is just an exercise for an assignment and I am sure the
tutor won't be digging deep into it (e.g. adding more data), I think
this will do the job for me.

Thank you guys again and I really appreciate you help and time.

 

[John Spencer]

Did you correct my typo in the query? Notice the name of the table is
misspelled in this line - UnitEnTrol instead of UnitEnrol

ON UnitEntrol.UnitCode = TopGrades.UnitCode

SELECT Student.SfName
, Student.SlName
, Student.PhoneNo
, UnitEnrol.UnitCode
, UnitEnrol.UnitGrade
FROM (Student INNER JOIN UnitEnrol
ON Student.StudentID = UnitEnrol.StudentID)
INNER JOIN
(SELECT Max(UnitGrade) as TopGrade, UnitCode
FROM UnitEnrol
GROUP BY UnitCode) as TopGrades
ON UnitEnrol.UnitCode = TopGrades.UnitCode
AND UnitEnrol.UnitGrade = TopGrades.TopGrade

You could modify this to return one of the students if there are ties. Or you
could use a TOP 1 query to force the return of just one student.

Since this is an exercise for an assignment, I won't give you the solution for
solving ties.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Zoran]

wow

It worked!

SELECT SfName, SlName, PhoneNo, UnitCode, UnitGrade
FROM (
SELECT Student.SfName, Student.SlName, Student.PhoneNo,
UnitEnrol.UnitCode, UnitEnrol.UnitGrade
FROM (Student INNER JOIN UnitEnrol ON Student.StudentID =
UnitEnrol.StudentID)
INNER JOIN
(SELECT Max(UnitGrade) as TopGrade, UnitCode
FROM UnitEnrol
GROUP BY UnitCode) as TopGrades
ON UnitEnrol.UnitCode = TopGrades.UnitCode
AND UnitEnrol.UnitGrade = TopGrades.TopGrade
)
WHERE UnitCode = "ICT100"

Thanks a lot for your help. This one was a little bit too complicated
for me.