report to lookup value not in database

[rubberhead10]

hello all,

I need to design a report that would look for an employee that has missed a certain training class.

I am using access 2007 and have the following tables tblemployeeInfo that has fields empID, empname, empgender and the table tblClassAttendance that has fields empid, className, classdate.

the problem I am having is that if an employee did not go to the class then there is not an entry in the tblclassattendance for the class. how would I set the report up to look for entries that are not there.....thanks

 

[JBinQLD]

Too easy mate, consider:

SELECT tblemployeeInfo.empID, tblemployeeInfo.empname, tblclassattendance.empID
FROM tblemployeeInfo LEFT JOIN tblclassattendance ON tblemployeeInfo.empID=tblclassattendance.empID
WHERE (((tblclassattendance.empID) Is Null));

 

[rubberhead10]

THANKS......IT looks great. I put it into the query but it gives me an error "Check the subquery's syntax and enclose the subquery in parentheses"...I just copied and pasted the query from what you typed........Thanks so much for your help

 

[JBinQLD]

I just pasted it into a test session here and get no error. Have i spelt your table/field names correctly? If that checks out ok then use the query builder to visually create this SQL.

a) Add the two tables concerned, tblEmployeeInfo and tblClassAttendance.

b) If a link doesn't automatically appear between the EmpID filed then use the mouse to drag one to the other.

c) Double click the link you've just create and choose the option "List ALL records from tblEmployeeInfo and only those from tblClassAttendance when related info exists"

d) add the fiels from tblEmplyeeInfo you wish to see.

e) Add the field EmpID from tblClassAttendance and add the criteria "Is null"

Let me know how you go. Curious error.

 

[rubberhead10]

Thanks it works perfectly......I uses the query builder and had no error.......thanks so much for your help