Keeping records with no results in query

[laina222]

I have a query in which I use two tables. Table 1 has city information in it and Table 2 has two fields, City and County. What I want to do is link each city to a county, which I can do, but there is one small problem. Any cities that were misspelled in the system are not included, so I want my query to include these misspelled cities and leave the County field blank. Is there any way this can be done?
Thanks for any help in advance.

 

[r937]

use a LEFT OUTER JOIN

i think in Access there's a wizard ("unmatched query"?)

you could just go to SQL View and change where it says INNER JOIN to LEFT OUTER JOIN

make sure the cities table is mentioned in the FROM clause first, before the other table

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[PHV]

Take a look at OUTER joins:
SELECT T1.City, T2.County
FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.City = T2.City;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JerryKlmns]

laina222, propably the join between those two tables is an Inner Join. Change it to Left Join or Right Join. It depends how the two tables are displaied on the query Design View. If you want to see all cities then use Left (Right) Join and place the table of cities on the left (right) side of the table that contains the county. Double-click the joining line between the two tables on the query Design View and select appropietly the join.
Left or Right doesn't matter, the result is the same. It just changes the SQL statement.