If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
With a Query in Access 2007, How can I Create This Query
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). |
#2
|
|||
|
|||
With a Query in Access 2007, How can I Create This Query
On 28 apr, 21:14, Terry wrote:
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). 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 |
#3
|
|||
|
|||
With a Query in Access 2007, How can I Create This Query
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. "XPS350" wrote: On 28 apr, 21:14, Terry wrote: 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). 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 . |
#4
|
|||
|
|||
With a Query in Access 2007, How can I Create This Query
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 "Terry" wrote: 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. "XPS350" wrote: On 28 apr, 21:14, Terry wrote: 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). 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 . |
Thread Tools | |
Display Modes | |
|
|