With a Query in Access 2007, How can I Create This Query

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

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).
 
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 onthis
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).
Click to expand...

A query to select records from T1 that don't have related records in
T2 should look like:
SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.ID-T1=T2.ID-T1

Access provides a wizard to create such queries.

Groeten,

Peter
http://access.xps350.com
 
Thanks, Peter. Your help below works well in finding information in table 2
that is not in table 1. What do I need to do to get the same results from
table 3, related to table 2, looking at table 1?
Thanks again.
 
Thanks, Peter.
In the suggestion below, is the "-T1" reference in T1.ID-T1 actually a
syntactical entry for the T1 table name?
Also, how can this be used to find all items in T3 that are not in T1? I
have a query that allows me to see the related fields in both, but the Query
Wizard doesn't show that saved query as a valid entry to look for the join.

T1.ID-T1=T2.ID-T1
 
Back
Top