distinct??

[Guest]

hi, is it possible to use distinct just on one field of a query. i've got a
query that returns students and their courses and learning hours. i know
that using distinct on this won't work because a student can have different
course codes, and therefore are presented more than once. i need to run a
query that will return the student and the course that has the most learning
hours only, so that each student is only presented once. any suggestions
would be appreciated

rhys

 

[Guest]

Assuming you have the student name, you will most likely need 2 queries. Or
a single Dx function.

Queries:

1) Do a Totals query on Student and course Hours. Group By Student and Max
on Hours.

2) Create a relation between the first query and your table, using Student
and Hours. This will return the matching record(s).

Or you could try a D function statement, first:

DMax("Hours", "tblTable1", "StudentName = [forms]![frmForm1]![StudentName]")

Again, take the Hours number, and the Student, and use as criteria to pull
the course name.

Good luck.

Sharkbyte

 

[Guest]

rhys:

You can also do this with a single query which includes a subquery. Lets
say you have tables Students, Courses, and StudentCourses, the last modelling
the many-to-many relationship between the first two and having columns
StudentID and CourseID referencing the primary keys of Students and Courses.
It also has a column LearningHours. The query would then go along these
lines:

SELECT Students.Student, Courses.Course, SC1.LearningHours
FROM Students, Courses, StudentCourses AS SC1
WHERE SC1.StudentID = Students.StudentID
AND SC1.CourseID = Courses.CourseID
AND SC1.LearningHours =
(SELECT MAX(LearningHours)
FROM StudentCourses AS SC2
WHERE SC2.StudentID = Students.StudentID);

The above assumes that each student has their own individual LearningHours
per course rather than each course having a fixed LearningHours value, in
which case the LearningHours column would be in Courses. If the latter is
the case the query would go like this:

SELECT Students.Student, C1.Course, C1.LearningHours
FROM Students, Courses AS C1, StudentCourses AS SC1
WHERE SC1.StudentID = Students.StudentID
AND SC1.CourseID = C1.CourseID
AND C1.LearningHours =
(SELECT MAX(LearningHours)
FROM StudentCourses AS SC2, Courses As C2
WHERE SC2.CourseID = C2.CourseID
AND SC2.StudentID = Students.StudentID);

In each of the above examples the subquery is correlated with the outer
query so that the MAX value for the current StudentID is returned, thus
restricting the outer query’s result set to those rows with this value for
LearningHours. The use of the aliases C1,C2 and SC1, SC2 isn’t strictly
necessary here as the correlation is not between two instances of the same
table, but they do help clarify the SQL I think.

Ken Sheridan
Stafford, England

 

[Guest]

thanks for that sharkbyte. it's works fine now if a student has learning
hours that are different. however some students are on different course that
have the same learning hours. ie
student code 123456,
course code cr987
learning hours 140,

and
student code 123456,
course code cr000,
learning hours 140.

how can i produce just one of these courses. it doesn't matter which one

thanks in advance
rhys

Assuming you have the student name, you will most likely need 2 queries. Or
a single Dx function.

Queries:

1) Do a Totals query on Student and course Hours. Group By Student and Max
on Hours.

2) Create a relation between the first query and your table, using Student
and Hours. This will return the matching record(s).

Or you could try a D function statement, first:

DMax("Hours", "tblTable1", "StudentName = [forms]![frmForm1]![StudentName]")

Again, take the Hours number, and the Student, and use as criteria to pull
the course name.

Good luck.

Sharkbyte

hi, is it possible to use distinct just on one field of a query. i've got a
query that returns students and their courses and learning hours. i know
that using distinct on this won't work because a student can have different
course codes, and therefore are presented more than once. i need to run a
query that will return the student and the course that has the most learning
hours only, so that each student is only presented once. any suggestions
would be appreciated

rhys