How to select records based on values in another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's a challenge:

I want to create a query against an address file that will select address records by checking a CityStateZip field against field values in a ForeignCountry table. The ForeignCountry table only has one field: the foreign country name. Ideally I want the query to select the record if any part of the CityStateZip field (that is, any substring - there may be more than just a country name in it) matches a value in any record of the ForeignCountry table. Is this possible? If so, how do I do it?

Thanks
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ideally, the CityStateZip column should be separated into 3 columns:
City, State, Zip.

The solution to your problem, for the combined column, could be like
this (JET SQL [aka Access SQL]):

SELECT T1.*
FROM Table1 As T1, ForeignCountry As F
WHERE Instr(F.CountryName, T1.CityStateZip) > 0

See the VBA Help article on Instr Function for more info.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQPhuvYechKqOuFEgEQLv8QCgtsDze5tXWutVJct0dTrKcS8LJgAAoOcO
lbiKmHQvyeuLmRUCbqyf4jGt
=EYzI
-----END PGP SIGNATURE-----
 
Back
Top