Multiple AND criteria for a single field (1 Viewer)

sherikream · Aug 14, 2018

Hi Everyone,

I'm trying to create a query that returns a count of courses a student has taken, ONLY if they've taken ALL the courses in a specified list, not if they've taken any one of the course in a list. If they're missing even one course from the list, they should not show up when I run the query.

I've tried adding multiple IN criteria in the same field using AND, but the run comes back empty. I've also tried adding IN criteria for the same field in separate columns for each course in the design view, run also comes back empty.

Code:

SELECT Courses.ID, Count(Courses.CourseID) AS CountOfCourseID
FROM Courses
WHERE (((Courses.CourseID) In ("089922") And (Courses.CourseID) In ("090589","090590")))
GROUP BY Courses.ID;

Help is appreciated, Thank you.

jdraw · Aug 14, 2018

We need info re CourseList, CourseStudentHasTaken

You might research the Unmatched Query Wizard tool in Access.

June7 · Aug 14, 2018

No record can meet that criteria.

Selection of record is conditional based on data in other records.

Need a query that selects students that do not have a required course. Then another query selects students that are NOT IN the first query. This is commonly done as nested query.

SELECT * FROM table WHERE student NOT IN(SELECT student FROM...);

I would need more info about your data structure to complete the SQL.

MajP · Aug 14, 2018

I am guessing that there is more to this. I assume there is a student ID in the table. Is the list being pulled from another table. Something like this

Code:

SELECT A.studentid, 
       Count(A.courseid) AS CountOfCourseID 
FROM   courses AS A 
WHERE   A.studentid  IN (SELECT B.studentid 
                              FROM   courses AS B 
                              WHERE  B.courseid IN ( "089922", "090589", 
                                                     "090590" ) 
                              GROUP  BY B.studentid 
                              HAVING Count(B.courseid) = 3) 
GROUP  BY A.studentid;

My guess that list is not a hard list but being pulled from a table. But there is no information provided.

arnelgp · Aug 15, 2018

here is my solution using UDF:

Code:

SELECT 
	Courses.StudentID, 
	fncCountOfCourseInList([StudentID],"089922","090589","090590") AS CountOfCourses
FROM Courses
GROUP BY 
	Courses.StudentID, 
	fncCountOfCourseInList([StudentID],"089922","090589","090590");

the function:

Code:

Option Compare Database
Option Explicit

Public Function fncCountOfCourseInList(ByRef ID As Variant, _
                                       ParamArray Courses() As Variant) As Variant
    '* total number of courses in the list to check
    Dim intTotalCoursesToCheck As Integer
    '* count of how many courses the student has takend
    Dim intCount As Integer
    Dim i As Integer
    Dim strCoursesFound As String
    intTotalCoursesToCheck = UBound(Courses)
    With CurrentDb.CreateQueryDef("", "SELECT CourseID FROM Students " & _
                                 "WHERE StudentID = @1")
        .Parameters(0) = ID
        With .OpenRecordset(dbOpenSnapshot)
            If Not (.BOF And .EOF) Then .MoveFirst
            While Not .EOF
                For i = 0 To intTotalCoursesToCheck
                    '* check if the student course is on the course list.
                    '* if the student has taken the course more than once
                    '* then only count is as 1.
                    If (Courses(i) = !Courseid) And (InStr(strCoursesFound, "," & Courses(i) & ",") = 0) Then
                        '* append the course to our variable
                        strCoursesFound = strCoursesFound & "," & Courses(i) & ","
                        intCount = intCount + 1
                    End If
                Next
                .MoveNext
            Wend
        End With
    End With
    '* count ALL courses in the list or NONE
    If intCount < intTotalCoursesToCheck + 1 Then intCount = 0
    fncCountOfCourseInList = intCount
End Function

Multiple AND criteria for a single field (1 Viewer)

sherikream

Registered User.

jdraw

Super Moderator

June7

AWF VIP

MajP

You've got your good things, and you've got mine.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread