Selecting one of duplicate records with a maximum value.

[Brad Visser]

I have a database with students results in it. Thay are able to retake exams
with the maximum result counting toward their qualification. I am battling to
get a query together which will sift out where there are duplicate module
results as well as selecting the result with the maximum value.

Those students where there is only one result present should also not be
excluded.

Fields in the table include
Student Number
Surname
Module
Mark
Session

 

[Marshall Barton]

I have a database with students results in it. Thay are able to retake exams
with the maximum result counting toward their qualification. I am battling to
get a query together which will sift out where there are duplicate module
results as well as selecting the result with the maximum value.

Those students where there is only one result present should also not be
excluded.

Fields in the table include
Student Number
Surname
Module
Mark
Session

I think you can use:

SELECT [Student Number], Surname, Module,
Max(Mark), Session
FROM table
GROUP BY [Student Number], Surname, Module, Session

 

[Brad Visser]

Thanks for this I have tried it but still get duplicate records showing, not
only the maximum value which I was trying to get.

I have a database with students results in it. Thay are able to retake exams
with the maximum result counting toward their qualification. I am battling to
get a query together which will sift out where there are duplicate module
results as well as selecting the result with the maximum value.

Those students where there is only one result present should also not be
excluded.

Fields in the table include
Student Number
Surname
Module
Mark
Session

I think you can use:

SELECT [Student Number], Surname, Module,
Max(Mark), Session
FROM table
GROUP BY [Student Number], Surname, Module, Session

 

[Marshall Barton]

I don't see how the query I posted can return more than one
record for a student/module/session combination.

Maybe you changed something in the query? Post a copy of
its SQL view so I can check it.

Or maybe I don't understanf what you mean by "duplicate
records" and you could try to explain it by posting some
sample records from both the table and the offending results
from the query.
--
Marsh
MVP [MS Access]

Thanks for this I have tried it but still get duplicate records showing, not
only the maximum value which I was trying to get.

SELECT [Student Number], Surname, Module,
Max(Mark), Session
FROM table
GROUP BY [Student Number], Surname, Module, Session

 

[Brad Visser]

Exam No Surname First Names Mark Module Session Grade
0001 Adams Samuel Scott 61 PH1 Jun-06 C
0001 Adams Samuel Scott 78 PH3 Jun-06 C
0001 Adams Samuel Scott 43 PH2 Jun-06 E
0001 Adams Samuel Scott 32 PH4 Jan-07 U
0001 Adams Samuel Scott 81 PH2 Jan-07 A
0001 Adams Samuel Scott 49 PH1 Jan-06 D

Hi Marshall

I have included the data from the table above. Where PH1 is the module
descriptor. When I run the query as set out I get the response below:

Exam No Surname Module MaxOfMark Session
0001 Adams PH1 49 Jan-06
0001 Adams PH1 61 Jun-06
0001 Adams PH2 43 Jun-06
0001 Adams PH2 81 Jan-07
0001 Adams PH3 78 Jun-06
0001 Adams PH4 32 Jan-07

SQL View below

SELECT PhysicsModules.[Exam No], PhysicsModules.Surname,
PhysicsModules.Module, Max(PhysicsModules.Mark) AS MaxOfMark,
PhysicsModules.Session
FROM PhysicsModules
GROUP BY PhysicsModules.[Exam No], PhysicsModules.Surname,
PhysicsModules.Module, PhysicsModules.Session;

Thanks in advance for your help.

Brad

I don't see how the query I posted can return more than one
record for a student/module/session combination.

Maybe you changed something in the query? Post a copy of
its SQL view so I can check it.

Or maybe I don't understanf what you mean by "duplicate
records" and you could try to explain it by posting some
sample records from both the table and the offending results
from the query.
--
Marsh
MVP [MS Access]

Thanks for this I have tried it but still get duplicate records showing, not
only the maximum value which I was trying to get.

Brad Visser wrote:
I have a database with students results in it. Thay are able to retake exams
with the maximum result counting toward their qualification. I am battling to
get a query together which will sift out where there are duplicate module
results as well as selecting the result with the maximum value.

Those students where there is only one result present should also not be
excluded.

Fields in the table include
Student Number
Surname
Module
Mark
Session

SELECT [Student Number], Surname, Module,
Max(Mark), Session
FROM table
GROUP BY [Student Number], Surname, Module, Session

 

[Marshall Barton]

I just got back from vacation and hope you didn't think your
question was abandoned.

I still can not see where there are any "duplicate" records
in the query's result set.

If you are referring to the different scores for different
sessions when you want to ignore the session, then just drop
all uses of the session field.
--
Marsh
MVP [MS Access]

Exam No Surname First Names Mark Module Session Grade
0001 Adams Samuel Scott 61 PH1 Jun-06 C
0001 Adams Samuel Scott 78 PH3 Jun-06 C
0001 Adams Samuel Scott 43 PH2 Jun-06 E
0001 Adams Samuel Scott 32 PH4 Jan-07 U
0001 Adams Samuel Scott 81 PH2 Jan-07 A
0001 Adams Samuel Scott 49 PH1 Jan-06 D


I have included the data from the table above. Where PH1 is the module
descriptor. When I run the query as set out I get the response below:

Exam No Surname Module MaxOfMark Session
0001 Adams PH1 49 Jan-06
0001 Adams PH1 61 Jun-06
0001 Adams PH2 43 Jun-06
0001 Adams PH2 81 Jan-07
0001 Adams PH3 78 Jun-06
0001 Adams PH4 32 Jan-07

SQL View below

SELECT PhysicsModules.[Exam No], PhysicsModules.Surname,
PhysicsModules.Module, Max(PhysicsModules.Mark) AS MaxOfMark,
PhysicsModules.Session
FROM PhysicsModules
GROUP BY PhysicsModules.[Exam No], PhysicsModules.Surname,
PhysicsModules.Module, PhysicsModules.Session;

I don't see how the query I posted can return more than one
record for a student/module/session combination.

Maybe you changed something in the query? Post a copy of
its SQL view so I can check it.

Or maybe I don't understanf what you mean by "duplicate
records" and you could try to explain it by posting some
sample records from both the table and the offending results
from the query.

Thanks for this I have tried it but still get duplicate records showing, not
only the maximum value which I was trying to get.

Brad Visser wrote:
I have a database with students results in it. Thay are able to retake exams
with the maximum result counting toward their qualification. I am battling to
get a query together which will sift out where there are duplicate module
results as well as selecting the result with the maximum value.

Those students where there is only one result present should also not be
excluded.

Fields in the table include
Student Number
Surname
Module
Mark
Session

:
SELECT [Student Number], Surname, Module,
Max(Mark), Session
FROM table
GROUP BY [Student Number], Surname, Module, Session