473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to match records with partially matching field data

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
7 13302
nico5038
3,080 Expert 2GB
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
Thanks. Used something similar based on another solution found here.

WHERE ((Right$([Parts3].[PRTNO],4)=Right$([Stores3].[ProductID],4)))
Nov 22 '06 #3
NeoPa
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
3,080 Expert 2GB
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

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

Similar topics

0
by: Dejan M. | last post by:
Please can you help me with this problem: In the next form, when I Add new person who is not registered in the base I had the massage: The Microsoft Jet database engine cannot find a record in...
2
by: Andrew Chalk | last post by:
I have a customer who wants to SELECT records based on a partial match in a text field. For example, in a list of telephone numbers they want to search for all records that contain the digits '777'...
3
by: Rnt6872 | last post by:
Hello All, I have a sql statement that I'm using to match records on. But i need to use a substring to get the nuber that I need, and I have to use several substrings to get the number since its...
0
by: MatchSQL | last post by:
http://www.MatchSQL.com Match your Design site's data schema and SQL statement to Application site between MSSQL 2000 , SQLServer 2005 , SQL Server 2005 EXPRESS
7
by: ebindia0041 | last post by:
This is like the bug from hell. It is kind of hard to explain, so please bear with me. Background Info: SQL Server 7.0, Asp.net 1.1 with c# I'm inserting simple records into a table. But one...
3
by: imtmub | last post by:
Hi All, I am facing some problem in the field data(Description field in Item table). In that table one field(Description field)data legth is 255 and type: nvarchar. In some operation this field data...
4
by: sufian | last post by:
Below is the field where user enters his/her email address and the AJAX post request is sent to the server and the user sees the message: echo("<div id=\"message\" class=\"success\">Thank you! You...
3
by: dmon000 | last post by:
I have a form that saves a diet order for a patient in an "Order Detail" table. Presently it saves one record for one particular meal (breakfast, lunch or dinner). Management now wants 3 records...
1
by: rafnavsun | last post by:
Hi Everybody, I need to concatenate all records on one field as one line. Example: Field 1 has 50 records, record1, record2 to record50. I need to display an output in Text that read as: ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.