Compare field using "start of"

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

Guest

I have two fields with customer name. One is copied from a tool that
sometimes brings spaces after the name. Creating a join eliminates matches
because of the spaces. How can I pull the customers who show up on the
customer table as well as this imported table when one has spaces at the end.
I think I need to specify that the Start of the field is the same, but not
sure how to do this. Thanks!
 
Create a query based on the imported table and include all the fields except
customer name. Rather than customer name, type the following into an empty
field in the query:
ImportedCustomerName:RTrim(CustomerName)

For your comparison query, use the customer table and the above query. Join
customer name in the customer table to ImportedCustomerName in the query.
 
Thanks, that worked. Will that also take care of a situation where the
customer name is ABC Corporation in one table and ABC in the other? As long
as they start with the same name? THanks again.
 
You will have to change the JOIN in the SQL View

SELECT ...
FROM TableA INNER JOIN TableB
ON TableA.CustomerName = TRIM(TableB.CustomerName)


OR less accurately
SELECT ...
FROM TableB INNER JOIN TableA
ON TableB.CustomerName Like TableA.CustomerName & "*"

The above queries assume that TableB.Customername is the one with the spaces.
The second option could give you false matches, since it would match "SMITHSON
" to "SMITH"
 
No, the Trim function only eliminates the spaces at the end of customer name
in the imported table.

For this situation, if the following assmptions are valid:
1. Customer name os two or more words
2. Customer name in imported table only uses the first word where the first
word is exactly the same as the first word in the customer table

Then do the following:
1. Add another field named CustomerFirstName to the customer table
2. Create an Update query based on the customer table and Update
CustomerFirstName to:
IIF(InStr([CustomerName],"
")=0,[CustomerName],Left([CustomerName],InStr([CustomerName]," ")-1))
3. Make a copy of your previous query joining the customer table to the
query. Join CustomerFirstName in the customer table to ImportedCustomerName
in the query.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
 
This is working. I've come across one more situation - Some customers come
through with XXXXimmediately before their name. Is there a way to disregard
the X's at the beginning of the field along with what I'm using from below?
This is what my query looks like now:

SELECT DISTINCT Customers.CustomerName, Customers.Type,
Compare.ImportedCustomerName
FROM Compare INNER JOIN Customers ON Compare.ImportedCustomerName Like
Customers.CustomerName & "*"
ORDER BY Customers.Type, Customers.CustomerName
WITH OWNERACCESS OPTION;
 
Hmm, possible but it will get slower

Try adding an OR clause to the ON clause. Something like the following UNTESTED
idea MAY work.

.... ON Compare.ImportedCustomerName Like Customers.CustomerName & "*"
OR Compare.ImportedCustomerName Like "XXXX" & Customers.CustomerName & "*"
....
 
Back
Top