Query one table against part of a cell of another

[apex77]

How do I create a query that matches the records in one
(tblLocationsInFtWorth) table based on part of the City field in another
(tblCities)? Example:
table 1 is the tblLocationsInFtWorth and table 2 s the tblCities table.

tblLocationsInFtWorth
LocationName
Joes of Arlington
Radisson TX Galviston

tblCities
City
Addison
Arlington
Bedford

 

[Michel Walsh]

Have a query with the two tables (your data table with the address and
tblCities )
Under the field address, use a criteria like:


LIKE "*" & City & "*"


That won't be fool-proof since you may have an address as: 777 Galviston
Street, New York

and the operator will find keep that record, since Galviston is also a city
(even if here, it is used as a street name).

If the City name, in the addresses, is always preceded by a coma, you can
try:

LIKE "*,*" & CIty & "*"



or, if the City is always last:

LIKE "*,*" & CIty


(but that won't pick, London, in, say, 123 Queen Avenue, London, Canada )


Vanderghast, Access MVP

 

[apex77]

Thanks Michael, but the first table (tblLocationsInFtWorth) does not have an
address field. It is a location name and the format is not always the same.

 

[Michel Walsh]

Then use the criteria

LIKE "*" & City & "*"

under the field Location, and be prepared to get 'false' detection (like
Galveston Street being assumed to be the city Galveston). You will likely
have false detection if an original record get more than one row in the
result. As example:


primaryKey Location City
1010 123 Galveston Street, New York Galveston
1010 123 Galveston Street, New York New York


the record with the primarykey value = 1010 will be listed twice (once
assuming the city is Galveston, once assuming the city is New York).

You can then delete the false detection, through MANUAL deletion (showing
initial records having more than row in the result).


Vanderghast, Access MVP