Joining Tables on Similar, but not identical, Fields?

[Gerry Goldberg]

I would like to join two tables but the join fields are not exact matches.
For example, table A may contain "Seattle" and table A may contain "Seattle,
WA". Is there any way to do this with the "Like" operator?

Thanks,

Gerry Goldberg

 

[John Vinson]

I would like to join two tables but the join fields are not exact matches.
For example, table A may contain "Seattle" and table A may contain "Seattle,
WA". Is there any way to do this with the "Like" operator?

Thanks,

Gerry Goldberg

With some limitations (such as the query not being updateable, yes).
Eg.

SELECT <whatever>
FROM TableA INNER JOIN TableB
ON TableA.City LIKE TableB.City & "*"

This will find records where TableA contains "Seattle, WA" and TableB
contains "Seattle", but not vice versa; and it will not find matches
between "Des Moines" and "DesMoines".

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Hi Does anyone know how to avoid duplicates using similar, but not exactly
the same value when joining two tables?

I have two tables, both contain a colume which will be able to match
exactly, that's colume "A", usually it's called "Lisa" or "Annie". Both Table
also contain a colume "B" which is similar, but not exact, usually it's
"200404022210" or "200404022215" ("yy/mm/dd/hh/mm"). When both Tables have
two records for "Lisa", how do I avoid getting 4 records in the select query
instead of just 2?

I tried select "Unique Value" to "yes" in query property, but that doesn't
work.

Thanks!