Results 1 to 5 of 5
  1. #1
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35

    Query to Find Multiple Matches in Same Field

    Hi,




    I have a table of student courses. There are a few columns but the key ones are StudentName and EnrolledCourses. The data are as the names suggest.


    Is it possible to run a query to find all students that have enrolled in both of two specific courses (Maths and English)? In other words the query should exclude any student that was not enrolled in only one of the two courses.


    Thanks,

    Rob

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That does get difficult.

    SELECT * FROM table WHERE EnrolledCourses="Maths" AND StudentName IN (SELECT StudentName FROM table WHERE EnrolledCourses="English");

    If you want more courses, will probably need a VBA custom function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi June,

    Thanks, this is great.

    Rob

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Another one (on fly)
    Code:
    SELECT m.StudentName
    FROM
         (SELECT StudentName FROM YourTable WHERE EnrolledCourses = 'Maths) AS m
         INNER JOIN
         (SELECT StudentName FROM YourTable WHERE EnrolledCourses = 'English") AS e ON e.StudentName = m.StudentName

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    and another one

    SELECT *
    FROM table INNER JOIN
    (SELECT StudentName FROM table WHERE EnrolledCourses="English") T ON table.studentname= T.studentname
    WHERE EnrolledCourses="Maths"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 33
    Last Post: 09-12-2019, 11:15 PM
  2. Replies: 11
    Last Post: 02-21-2016, 02:34 PM
  3. Replies: 1
    Last Post: 11-08-2012, 02:37 PM
  4. Replies: 2
    Last Post: 10-20-2011, 08:31 AM
  5. Help: Comparison query to find non-matches
    By 14erclimber in forum Queries
    Replies: 6
    Last Post: 06-09-2010, 09:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums