CODEWITHSUNDEEP
sqlms-accessms-access-2007
Chuck
Chuck

Reputation: 1226

Comparing fields to find none matches

SELECT Left([PTC Import].[ZipCode],5) AS Expr1, [PTC Import].[Country], [ZipCodeDatabase_STANDARD].[ZipCode]
FROM [PTC Import] 
LEFT JOIN [ZipCodeDatabase_STANDARD].[ZipCode] ON Expr1 = [ZipCodeDatabase_STANDARD].[ZipCode]
WHERE ((([ZipCodeDatabase_STANDARD].[ZipCode]) Is Null) AND (([PTC Import].[Country])="USA"
FROM [PTC Import], ZipCodeDatabase_STANDARD;

Ok what I have here is my attempt to compare the ZipCode field from my import to our ZipCode database to validate and make sure it is in fact a valid zip code. I would like this query to display any records that do not match a valid zip code where the country is USA.

I am currently getting a syntax error in JOIN statement error message.

I have the ZipCode db linked from another Access db if that would affect anything.

Upvotes: 0

Views: 43

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

The last line 

FROM [PTC Import], ZipCodeDatabase_STANDARD;

shouldn't be there. I think you had an inner join and forgot to remove it:

Then you have an error in your LEFT JOIN with [ZipCodeDatabase_STANDARD], where you are using an alias you defined in FROM. You should use Left(i.[ZipCode],5), since the alias isn't defined yet.

Additionally, i added an alias for each table to make it easier to read.

It should be:

SELECT Left(i.[ZipCode],5) AS Expr1, 
       i.[Country], 
       zs.[ZipCode]
FROM [PTC Import] i 
LEFT JOIN [ZipCodeDatabase_STANDARD] zs ON Left(i.[ZipCode],5) = zs.[ZipCode]
WHERE zs.[ZipCode] Is Null AND i.[Country])="USA";

Upvotes: 3

HansUp
HansUp

Reputation: 97131

Your LEFT JOIN seems to be trying to join a table with a field.

FROM [PTC Import] 
LEFT JOIN [ZipCodeDatabase_STANDARD].[ZipCode]

Join a table to another table instead. Perhaps this is what you need.

FROM [PTC Import] 
LEFT JOIN [ZipCodeDatabase_STANDARD]

For the join's ON clause, I think you want ...

ON [PTC Import].[ZipCode] = [ZipCodeDatabase_STANDARD].[ZipCode]

And then eliminate the second FROM clause.

If you have Access' query designer available, use that to create your query. At least set up the join from the designer because it knows how to create joins which keep the db engine happy.

Upvotes: 2

Related Questions