Query To Check for Missing Records

[sherlocked]

Hello experts,

I am attempting to write a query that will allow a user to select a particular class from a dropdown list and check to see who HAS NOT attended the class.

I have two tables, tblEmployees and tblAttendance. The linked field is EmpNo.

Below is what I have come up so far but I get no results in my query even though I know there are folks who have not attended the class.

What am I doing wrong? Your assistance as always is appreciated

SELECT tblEmployees.EmpNo, tblAttendance.ClassDate, tblAttendance.ClassName
FROM tblEmployees LEFT JOIN tblAttendance ON tblEmployees.EmpNo = tblAttendance.EmpNo
WHERE (((tblAttendance.EmpNo) Is Null) AND ((tblAttendance.ClassName)="CLASS NAME FROM DROPDOWN HERE"));

 

[arnelgp]

You can also use sub query:


SELECT tblEmployees.EmpNo FROM tblEmployees where tblEmploees.EmpNo Not In (Select tblAttendance.EmpNo From tblAttendance
WHERE ((tblAttendance.ClassName)="CLASS NAME FROM DROPDOWN HERE")));

 

[sherlocked]

That seems to have worked! Here's an additional level of complexity for you:

Each of my classes has a required frequency. For example, Class1 is required annually, but Class2 is only required to be taken one time.

Can we adapt this query to check for the frequency requirement of the class selected, and then check to see who HAS NOT taken the class within the required timeframe?

(for example, Susie may have taken Class1 two years ago, and John may have taken it last year. I want to see Susie's name in my query results, not John's.)

Hope this makes sense. Thanks

 

[arnelgp]

For class2 that wont be a problem we can use the existing query.
For class1 you need additional criteria:

Last attendace year= year(date())

 

[sherlocked]

Thank you - just curious though, shouldn't we check to see what the frequency of the class is first? Some are annual, some are bi-annual, some are monthly, and some are one time. Don't we need to tell the query how far back to look, based on the frequency requirement?

 

[sherlocked]

I have tried another method - saving the frequency of the class in a variable and using Case statements to guide the recordset for my report.

However, I am now getting the below error message when trying to use this SQL statement for my recordset:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause."

Any ideas?

SELECT tblEmployees.EmpNo, [eLName] & ', ' & [eFName] AS FullName
From tblEmployees
WHERE (((tblEmployees.EmpNo) Not In (Select tblAttendance.EmpNo, tblAttendance.ClassName, tblAttendance.ClassDate From tblAttendance
WHERE ((tblAttendance.ClassName)= "CLASS NAME FROM DROPDOWN HERE")))) AND tblAttendance.ClassDate < Date()-365

 

[arnelgp]

Remove the Classname and classdate its not needed from the select statement

 

[sherlocked]

I think i've figured it out Below is what I went with, for any curious folks who come after me!

Private Sub Report_Load()
Dim Frequency As String

Frequency = DLookup("Frequency", "tblClassList", "ClassName = '" & Forms!frmSupDash2!cmboClassName & "'")

Select Case Frequency

Case "Annual"

Me.RecordSource = "SELECT tblEmployees.eEmpID, [eLName] & ', ' & [eFName] AS FullName " _
                & "From tblEmployees " _
                & "WHERE (((tblEmployees.eEmpID) Not In (Select tblAttendance.EmpNo From tblAttendance " _
                & "WHERE ((tblAttendance.ClassName)= '" & Forms!frmSupDash2!cmboClassName & "' AND tblAttendance.ClassDate > Date()-365))))"
                
Case "Bi-Annual"

Me.RecordSource = "SELECT tblEmployees.eEmpID, [eLName] & ', ' & [eFName] AS FullName " _
                & "From tblEmployees " _
                & "WHERE (((tblEmployees.eEmpID) Not In (Select tblAttendance.EmpNo From tblAttendance " _
                & "WHERE ((tblAttendance.ClassName)= '" & Forms!frmSupDash2!cmboClassName & "' AND tblAttendance.ClassDate > Date()-730))))"
                
Case "Three Years"

Me.RecordSource = "SELECT tblEmployees.eEmpID, [eLName] & ', ' & [eFName] AS FullName " _
                & "From tblEmployees " _
                & "WHERE (((tblEmployees.eEmpID) Not In (Select tblAttendance.EmpNo From tblAttendance " _
                & "WHERE ((tblAttendance.ClassName)= '" & Forms!frmSupDash2!cmboClassName & "' AND tblAttendance.ClassDate > Date()-1095))))"
                
Case "Monthly"

Me.RecordSource = "SELECT tblEmployees.eEmpID, [eLName] & ', ' & [eFName] AS FullName " _
                & "From tblEmployees " _
                & "WHERE (((tblEmployees.eEmpID) Not In (Select tblAttendance.EmpNo From tblAttendance " _
                & "WHERE ((tblAttendance.ClassName)= '" & Forms!frmSupDash2!cmboClassName & "' AND tblAttendance.ClassDate > Date()-32))))"
                
Case "One Time"
Me.RecordSource = "SELECT tblEmployees.eEmpID, [eLName] & ', ' & [eFName] AS FullName " _
                & "From tblEmployees " _
                & "WHERE (((tblEmployees.eEmpID) Not In (Select tblAttendance.EmpNo From tblAttendance " _
                & "WHERE ((tblAttendance.ClassName)= '" & Forms!frmSupDash2!cmboClassName & "'))))"
End Select

End Sub