Joining 2 tables in Access query.

[dlsmart]

I have 2 tables - 'Nurseries' with nursery name and address info, and 'Zip' with 2 fields -zip1 and zip2. Zip1 are unique (but multiple) values and zip2 are zip codes with an area around zip1. Example:
zip1 zip2
-----------------
94041 94036
94041 94303
94041 95051
94041 95054
... etc.

When the user enters a zip code (like 94041), I'm trying to get a list of nurseries with zip codes in the zip2 list.

I've tried inner joins and right and left joins, and with each I get syntax errors. Example of code:

SELECT *
FROM Nurseries
INNER JOIN Zip
ON nurseries.zip = zip.zip2
WHERE nurseries.zip IN (SELECT zip.zip2 WHERE zip.zip1 = '[Enter zip:]);
 

Any help would be greatly appreciated.

[jforbes]

This should probably do it. I just typed it in, so beware of typos.

You would want to Query on the Zip you have, then get a list of associated Zips and Nurseries that have that Zip.

SELECT Nurseries.*
FROM Zip
LEFT JOIN Nurseries
ON Zip.Zip2=Nurseries.Zip
WHERE Zip.Zip1=[TheZipYouAreQueryingOn]

[dlsmart]

Thanks for the reply. It does work, but I would get several blank lines between the nurseries listed in the dataset view. I tried changing the LEFT JOIN to a RIGHT JOIN and the resulting contained the same resulting nurseries with no blank line between.