Reply to thread

Message: [QUOTE="Terry, post: 13951913"] I need a query that will list all records in table 1 for which there are no auditor records (Table 3). Somehow, I need to use the relationship between tables 2 and 3 to find what's not in table 1. The following query gives me a list of all records that do have auditor records. I'm at a dead end on this one. Query SELECT PTOTNamesTbl.PTOTAuditingTherapist, PTOTAuditingTherapist.PTOTFirstName, PTOTAuditingTherapist.PTOTLastName, AuditDetailInitialEval.TherapistLastName, PTOTNamesTbl.PTOTFirstName, PTOTNamesTbl.PTOTLastName, AuditDetailInitialEval.Medicare, AuditDetailInitialEval.UniversityNumber, AuditDetailInitialEval.DateofService INTO tblCompletedAudits FROM PTOTNamesTbl AS PTOTAuditingTherapist INNER JOIN (PTOTNamesTbl INNER JOIN AuditDetailInitialEval ON PTOTNamesTbl.PTOTNamesID = AuditDetailInitialEval.TherapistLastName) ON PTOTAuditingTherapist.PTOTNamesID = PTOTNamesTbl.PTOTAuditingTherapist WHERE (((AuditDetailInitialEval.Medicare)=On) AND ((AuditDetailInitialEval.DateofService) Between #12/31/2008# And #1/1/2010#)) ORDER BY PTOTNamesTbl.PTOTAuditingTherapist; Table 1 Detail record, contains contactID1 as a foreign key. Table 2 Metadata table contains contact information ContactID1 (primary key), relationship between Table 2 (one) and Table 1 (many) Table 3 (virtual) Table 2 also has another ContactID2 field that is associated with ContactID1 (in the query design window, I add a second table 2 and give it an alias of auditor and related it to ContactID1 of Table 1). [/QUOTE]

Verification