Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Unable to Filter a Query to give me list of Employees

    Dear All,

    I have two tables TbleStudents and TblCourses.
    I want to to filter names of all those students who have attended a course From 05/05/11 until now. So I added the following fields in my query:

    StudentName StudentSurname DateOfCourse and in the criteria row under the DateofCourse i wrote >05/05/11.

    However, the query gives me name of each student several times because each student has attended many classes BUT I only want each employees names to appear once, as long as they have attended a course on any date from 05/05/11 to date.

    Any help would be much appreciated.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Apr 2011
    Posts
    34
    In the query design view, right click the window. Select Unique values=yes

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Even selecting unique values will give you multiple rows in the output if an employee has been on courses on more than one day.
    Try:
    Code:
    SELECT StudentName, StudentSurname, Min(DateOfCourse) AS FirstCourseDate
    FROM [TableName]
    WHERE DateOfCourse  > #05/05/2011#
    GROUP BY StudentName, StudentSurname
    For every student/employee who has attended a course since 05/05/2011, this will give you their name, surname and the date of the first course.
    You can add other aggregate functions into the SELECT statement for other information, e.g.:
    Code:
    Max(DateOfCourse) AS LastCourseDate
    Count(DateOfCourse) AS NumberOfCourses
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Apr 2011
    Posts
    34
    As pointed out by weejas, my suggestion above would work only if DateOfCourse is not part of the query result which is what I thought you wanted.
    SELECT DISTINCT StudentName, StudentSurname
    FROM [TableName]
    WHERE DateOfCourse > #05/05/2011#

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •