Pulling Data from Two Tables

[KWilkins]

Table 1 has street names (Smith St) and table 2 has customer addresses (123
Smith St). Is there a way to pull only the addresses that match the street
names from table 1? I have 30 streets that I will be using to cross
reference table 2 which has over 1000 records.

 

[Lance]

You are probably not going to be able to get 100% accurate results, but you
can try the LIKE statement.

 

[Jeff Boyce]

As Lance alludes to, there's no guarantee that the person entering "123
Smith St." and "Smith Street" spelled them the same.

And it may come as a surprise, but I've run across references to the same
city containing MORE than one instance of a street name, so which one does
"Smith Street" match?!

If you need to know what street names you have in your second table, perhaps
you could simply "dump" the street number portion ... does the remainder
give you what you want?

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Ron2006]

And of course there is the same street name in different cities and/
or different States and/or different ZipCodes..

And there is the
North First St vs No. First Steet vs North 1st St. vs
North First Str.


Have fun.

 

[KWilkins]

I think the problem will be that the house # and street name are together
instead of split into two fields. I don't think there is a way to run a
query because of that.

The end user who uses this database also is not consistent with entering St,
Street, St., etc.

Yes, we have the same street in the same city spelled two different ways
depending on each end of the street you live on. There's no way a query will
be 100% accurate.