If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query one table against part of a cell of another
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 |
#2
|
|||
|
|||
Query one table against part of a cell of another
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" wrote in message ... 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 |
#3
|
|||
|
|||
Query one table against part of a cell of another
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" wrote: 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" wrote in message ... 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 |
#4
|
|||
|
|||
Query one table against part of a cell of another
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 "apex77" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|