3 Replies Latest reply on Jan 23, 2013 3:30 PM by Soumitra Godbole

    How to filter based on multiple values of a single field with AND condition

    Soumitra Godbole

      Example : We have 5 Students (A, B, C, D & E) who register for 6 different Mathematics Course Numbers (MATH101, 102, 103, 104, 105 & 106). The Database with the above 2 fields (Student name and Course Number) is shown below

       

      Student       Course

      Name          Number

       

        A                 101

        A                 102

        A                 104

        B                 102 

        B                 103

        B                 104

        C                 102

        C                 104

        C                 106

        D                 101

        D                 102

        D                 105

        E                 101

        E                 106

       

      We have the Course Filter (Multiple value list) 101, 102, 103, 104 & 105. What we are trying to determine is the Number of students who are registered for multiple course numbers.

       

      Now, if we select 102 & 104 then we should see only 3 Students (A, B & C) and similarly if we select 101 & 102 then we only have 2 students (A & D). I have a solution using Windows_Max which works when we have common courses but it does fail when we select courses that no one has in common Eg:- 102 & 106.

       

      Basically what we are trying to achieve is use Multiple values for the Course Name filter and get the names of Students registered for just those selected Courses (AND condition).