473,890 Members | 1,394 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How Do I Link Two Tables with Only a Partial Match

6 New Member
one table shows 123456 in ID the other table shows 123456/1

I want to return records where these two fields match but I don't want to specify the actual number, just where these fields match, even if it is partially. Help !
Oct 6 '11 #1
15 22654
988 Recognized Expert Contributor

In what context are you working? Access? Or through ODBC?

Oct 6 '11 #2
12,516 Recognized Expert Moderator MVP
You can use the LIKE predicate with wildcards.
Oct 6 '11 #3
6 New Member
I am using Access. I want to retrieve records that match the numbers as shown above. The field is the primary key.
Oct 6 '11 #4
12,516 Recognized Expert Moderator MVP
I refer you back to post 3.
Oct 6 '11 #5
26 New Member

For exact matches you just need to create a select query with a join on the two fields you want matched. Try the query wizard. For partial matches, it depends on what you want to match. Rabbit is correct that you need to use the LIKE predicate with wildcards. If you will respond with exactly what type of partial match i.e. field in table2 must start with all characters in ID field in table1 and can have extra characters following, or field in table2 must have all characters in ID field in table1 and can have characters preceding and/or following. Then we can give you more specific help on how to create your query.

Oct 6 '11 #6
6 New Member
I am looking for, if the 7 characters in table one, match the first 7 characters in table 2 (table 2 having more characters)then consider that a match.
Oct 6 '11 #7
26 New Member
In the select query's criteria row, in the table2 field that needs to partially match, enter the following - Like [Table1.ID] & "*". Just be sure to replace "Table1.ID" with the acutal table and field names.
Oct 6 '11 #8
32,584 Recognized Expert Moderator MVP
I would suggest :
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl1.*
  2.      , tbl2.*
  3. FROM   [tbl1]
  4.        INNER JOIN
  5.        [tbl2]
  6.   ON   tbl2.ID Like tbl1.ID & '/*'
A little more thought given to expressing what you actually require help with would be appreciated. Working with so very little information provided is unnecessarily difficult.
Oct 6 '11 #9
6 New Member
OK. I have two tables. In table 1 the ID field contains a 7 digit number. In table 2 the ID field contains the same 7 digit number with /1 added. These fields are the primary key fields. JAGsters suggestion works great if I use another field as the primary key.
Oct 7 '11 #10

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

Similar topics

by: Mark | last post by:
Hi all, I have 2 files containing Id numbers and surnames (these files essentially contain the same data) I want to select distinct() and join on id number to return a recordset containing every individual listed in both the files HOWEVER, in some cases an incomplete ID number has been collected into one of the 2 files -is there a way to join on partial matches not just identical records in the same way as you can select where LIKE...
by: nospam | last post by:
Ok, I asked this question before and I also looked at the book "First Look at ASP.NET 2.0" I also read Paul wilson's web page explanation. HOWEVER...... The book and that web page talks about partial types and that IF you have a Extends keyword, I think, before the class name AND the same Namespace, .NET will automatically compile the rest of the class that is located in another file. However, I want to know just exactly how is .NET...
by: Jon Slaughter | last post by:
struct NullClass { void Null() { } }; template <unsigned int i, typename T> struct Node { enum {I = i}; typedef T Class; };
by: Erik Wikström | last post by:
In school (no I will not ask you to do my schoolwork for me) we talked about policy-based design and got an assignment where we got the a code- fragment from a stack-implementation. The idea with the code was that if, when its destructor was called, it still contained any elements it would run 'delete' on them if they were pointers and do nothing if they were not. Our assignment was to reimplement the stack using policies so that the...
by: Larry | last post by:
I have the following code attached to a search button on a form that runs a query. It works great, except that the search for Last Name only returns exact matches. It is even case sensitive. Anybody have an idea of what I need to add or change. It is the first If Then statement that I need to search on partial matches. I've tried to use the like operator, but I just get syntax errors. If Not IsNothing(Me.LastName) Then strSearch = "= "...
by: B Moor | last post by:
I have a database with 100,000's records, each with a unique reference, eg A123BNK456 I would like to generate a search facility whereby we can choose an exact match or partial match, where the partial match could be any of the following:- Partial match 1: 1 character maybe wrong Partial match 2: 2 characters maybe wrong Partial match 3: 3 characters maybe wrong
by: veaux | last post by:
Question about joins in queries. I have 2 tables with a field called "ID". Table 1 Rec1 = Jan12FredFlintstone Rec2 = Feb01WilmaRubble Table 2 Rec1 = Jan12BarneyRubble Rec2 = Mar03SamSlate
by: Edwin Smith | last post by:
Hello: I have the following code which works on a DataGridView binding source: private void textBox1_TextChanged(object sender, EventArgs e) { this.pATIENTSBindingSource.Position = this.pATIENTSBindingSource.Position("Name", textBox1.Text); }
by: gyap88 | last post by:
I m currently creating a search engine for my database using visual basic 2005. I want to my program to return search result even if the user only enter PARTIAL matches. Dim city As String city=Textbox1.Text Dim cityfound As Boolean = False For i As Integer = 0 To (dt.Rows.Count - 1) If CStr(dt.Rows(i)("Cities").ToString.ToUpper) = city.ToUpper Then accessionfound = True rowIndex = i
by: chungiemo | last post by:
Hi thought I would do another thread as this one is a bit different from the previous problem I am looking for a solution to the relating problem Comparing 2 access databases with 2 tables, they are non-related tables, but should have been related by the ID, and reason for this was due to the poor data entry standards. I am trying to compare both databases with a Partial ID in Table 1 and match it to the Full ID in Table 2 with the...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.