Querying values that are in one table but not the other 1

[jamez05]

Hi,

its been awhile since I used Access jet engine. I'm having a problem with the following query. I set it up how I normally do using other databases, but it returns zero results when I try to run it in access.

I want to get the values from the temp table that do not exist in the site table. Not sure what I'm doing wrong:

         strSQL = "SELECT "
         strSQL = strSQL & "DISTINCT tmp.Rec_ID, tmp.[Site ID]"
         strSQL = strSQL & " FROM tmpExcelUpload tmp WHERE tmp.[Site ID] NOT IN(SELECT [tblSite-type].[Site ID] FROM [tblSite-type])"

 

[Remou]

That seems ok, it works for me. Could the field types be different?

 

[jamez05]

both the fields are text fields. Thought I would try
adding single quotes around the not in statement but that returns all records instead of none. Not sure why this isn't working.

 

[Remou]

Have you tried pasting the SQL into the query design screen?

 

[jamez05]

Yes, with no luck. I got the following to work as a query, but i can only get it to select one field instead of two (works with site id, but when I try to select rec_id as well it errors out)

SELECT tmpExcelUpload.[Site ID]
FROM [SELECT tmpExcelUpload.[Site ID], [tblSite-Type].[Site ID] FROM tmpExcelUpload LEFT JOIN [tblSite-Type] ON tmpExcelUpload.[Site ID] = [tblSite-Type].[Site ID]]. AS [%$##@_Alias]
WHERE ISNull([tblSite-Type].[Site ID]);

 

[Remou]

Try:

SELECT tmpExcelUpload.Rec_ID,tmpExcelUpload.[Site ID]
FROM tmpExcelUpload LEFT JOIN [tblSite-Type] ON tmpExcelUpload.[Site ID] = [tblSite-Type].[Site ID]
WHERE [tblSite-Type].[Site ID] Is Null

 

[jamez05]

Thank You! - that was driving me nuts