469,266 Members | 2,048 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

How Do I Link Two Tables with Only a Partial Match

6
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 21135
Oralloy
983 Expert 512MB
Suzemt,

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

Cheers,
Oralloy
Oct 6 '11 #2
Rabbit
12,516 Expert Mod 8TB
You can use the LIKE predicate with wildcards.
Oct 6 '11 #3
Suzemt
6
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
Rabbit
12,516 Expert Mod 8TB
I refer you back to post 3.
Oct 6 '11 #5
JAGster
26
Suzemt,

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.

Regards
JAGster
Oct 6 '11 #6
Suzemt
6
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
JAGster
26
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
NeoPa
32,171 Expert Mod 16PB
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 & '/*'
@Suzemt
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
Suzemt
6
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
NeoPa
32,171 Expert Mod 16PB
You don't even comment on my suggestion in post #9. It would be nice to know if it worked or failed. That doesn't seem to be asking too much.
Oct 7 '11 #11
Suzemt
6
Sorry! no rudeness intended. I was looking for the more simple approach of just a select query without SQL. I may need to pass this on to less adept Access users. All advice is most gratefully accepted.
Oct 7 '11 #12
Suzemt
6
By passing on to less adept users I was referring to the data base I have, not my query!!!!
Oct 7 '11 #13
JAGster
26
Suzmet, The select query with the statement in the criteria field is the simplest way I know of for doing what you want.

The statement for the criteria field is - Like [Table1.ID] & "*"

You just have to replace "Table1.ID" with the name of your first table and the name of the ID field.

Here is a screenshot of a sample query (mouseover it to expand the image)



Let me know if this works or if you have other questions.
Regards, JAGster
Oct 7 '11 #14
NeoPa
32,171 Expert Mod 16PB
Suzemt:
Sorry! no rudeness intended.
I can accept that Suze. I appreciate that different people find different things to be outside their comfort zones. That's no problem if you choose to use JAGster's offering instead. I was just hoping for some feedback as to whether or not it worked when you tested it (I assume you tested it). It may also help others who are looking for solutions to similar problems to find a post explaining whether or not any particular suggestion worked.
Oct 7 '11 #15
Such an elegantly simple fix - thanks JAGster!
Mar 18 '19 #16

Post your reply

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

Similar topics

reply views Thread by Mark | last post: by
reply views Thread by Jon Slaughter | last post: by
2 posts views Thread by B Moor | last post: by
3 posts views Thread by Edwin Smith | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.