By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

How to match records with partially matching field data

P: 3
I have 2 Access tables. One has a field with a 4 character product id. Example data: "3210". The other table has the same product id's for matching records BUT has leading characters: "RD3210", etc.

I want to match records in an Access query that will match the fields based on the last 4 characters, which fortunately, are unique in both tables. I can edit in SQL View if necessary.

Thanks for any help.
Nov 22 '06 #1
Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
Just create first a query like:

select Mid([keyfield],3) as TruncKey, ...

Now you can JOIN the query and this table on TruncKey and the numeric key.

Getting the idea ?

Nic;o)
Nov 22 '06 #2

P: 3
Thanks. Used something similar based on another solution found here.

WHERE ((Right$([Parts3].[PRTNO],4)=Right$([Stores3].[ProductID],4)))
Nov 22 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
You don't need the Right() of the field which is already 4 chars long.
Right() is recommended over Right$().
That's a good concept, but the execution is probably a bit inefficient. What you really need is a JOIN.
To do this produce a sub-query of the table with the extra characters, modified to return Right([YourField],4), and JOIN that to the other table in your SQL.
An example of a sub-query might be :
Expand|Select|Wrap|Line Numbers
  1. SELECT subQuery.*
  2. FROM (SELECT * FROM [YourTable]) AS subQuery
Nov 22 '06 #4

P: 3
This makes perfect sense.

But now I realize there are records with apha characters on the right side and both sides of the numbers. "3210RD" Unfortunately, there's not a consistant number of these characters: "3210RDK" and even "BL3210-R"

Is there a way to trim just alpha characters - with an unspecified number of these characters on both sides of the numeric characters? Or that extracts only numeric values on the one table so I can then use it with the other (numeric only) table/field - as you suggested. Or other code that matches only the numeric values between the two tables/fields?

Thanks
Nov 22 '06 #5

NeoPa
Expert Mod 15k+
P: 31,660
I believe not.

You could write a public Function to do it stored in an ordinary Module.
That's the best way I can think of.
Nov 22 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
I have 2 Access tables. One has a field with a 4 character product id. Example data: "3210". The other table has the same product id's for matching records BUT has leading characters: "RD3210", etc.

I want to match records in an Access query that will match the fields based on the last 4 characters, which fortunately, are unique in both tables. I can edit in SQL View if necessary.

Thanks for any help.
If you really want to fix the problem.

Add a field to the second table with the alpha characters removed. Change the datatype of both fields to numbers. Much more efficient.

You don't have to replace the alphanumeric field but adding the second field will make joining the tables much easier.

Mary
Nov 22 '06 #7

nico5038
Expert 2.5K+
P: 3,072
As described it's best to use a function like:
Expand|Select|Wrap|Line Numbers
  1. function fncNumber (strInput as string) as string
  2.  
  3. dim intI as integer
  4.  
  5. for intI = 1 to len(strInput)
  6.    if mid(strInput,intI,1) >= "0" and mid(strInput,intI,1) <= "9" then
  7.       fncNumber = mid(strInput,intI,1)
  8.    endif
  9. next
  10.  
  11. end function
  12.  
Copy/paste this above code into a module and use in your query:

select fncNumber([your field with characters]) as NewNumberKey, ...

Nic;o)
Nov 23 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.