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

    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.
    Love begets Love, Help Begets Help

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

  3. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    Even selecting unique values will give you multiple rows in the output if an employee has been on courses on more than one day.
    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.:
    Max(DateOfCourse) AS LastCourseDate
    Count(DateOfCourse) AS NumberOfCourses
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #4
    Join Date
    Apr 2011
    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