Query one table against info in other Table

[jayceesdj]

I have a query that pulls data from 3 tables combining those tables and limiting the information to data that is less than 60 days old and doesn’t contain specific information in one of the fields. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I also need this same query or another query to further limit this data based on another table. <o></o>
<o></o>
If the field contains data that is on Narr_table code_1 field I don’t want to include it.<o></o>
<o></o>
The sql is working for everything except the Narr_table part. These fields could change on a regular basis so I don’t want to write them in as criteria. I would rather have the criteria check the table for matching information. Currently there are code_1 fields.<o></o>
<o></o>
The current working SQL looks like the below:<o></o>
<o></o>

<o:p></o:p>
SELECT Primary_Table.KeyNo, Primary_Table.LogDate, Primary_Table.Totincome, Primary_Table.Pname, Primary_Table.PAddr1, Primary_Table.PAddr2, Primary_Table.PCity, Primary_Table.PSt, Primary_Table.PZip, Primary_Table.PHP, Primary_Table.SSO, Prim_Lines_of_Trade.P_AD, Prim_Lines_of_Trade.P_last_rprtd, Prim_Lines_of_Trade.P_Bal, Prim_Lines_of_Trade.P_Mop, Prim_Lines_of_Trade.P_pymt, Prim_Lines_of_Trade.[1Narr], Prim_Lines_of_Trade.[2Narr], Prim_AA_Codes.Pdecl_1, Prim_AA_Codes.Pdecl_2, Prim_AA_Codes.Pdecl_3, Prim_AA_Codes.Pdecl_4<o:p></o:p>
FROM (Primary_Table INNER JOIN Prim_Lines_of_Trade ON Primary_Table.KeyNo = Prim_Lines_of_Trade.KeyNo) INNER JOIN Prim_AA_Codes ON Prim_Lines_of_Trade.KeyNo = Prim_AA_Codes.KeyNo<o:p></o:p>
WHERE (((Prim_Lines_of_Trade.P_last_rprtd)>Date()-60) AND ((Prim_Lines_of_Trade.P_Mop)<"08"));<o:p></o:p>
<o:p></o:p>

<o></o>

 

[Joe4]

It sounds like you want an unmatched query, that is, you want to return records from object 1 that do not have matching records in object 2.

Here is an example that will return all records in Table1 that do not have matching records in Table2:

SELECT Table1.*
FROM Table1 
LEFT JOIN Table2 
ON Table1.Field1 = Table2.Field1
WHERE Table2.Field1 Is Null

So you just want to employ the same concept in your query. The key is that it needs to be a LEFT JOIN, and you must have the WHERE clause.

You should be able to incorporate that into your code. If you are having trouble incorporating it all into your existing query and need to simplify it, save the code you posted above as a Custom View, and use that Custom View and your other Table in the query structure I posted above.