How Do I Link Two Tables with Only a Partial Match

[Suzemt]

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 !

[Oralloy]

Suzemt,

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

Cheers,
Oralloy

[Rabbit]

You can use the LIKE predicate with wildcards.

[Suzemt]

I am using Access. I want to retrieve records that match the numbers as shown above. The field is the primary key.

[Rabbit]

I refer you back to post 3.

[JAGster]

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

[Suzemt]

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.

[JAGster]

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.

[NeoPa]

I would suggest :

SELECT tbl1.*
     , tbl2.*
FROM   [tbl1]
       INNER JOIN
       [tbl2]
  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.

[Suzemt]

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.

[NeoPa]

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.

[Suzemt]

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.

[Suzemt]

By passing on to less adept users I was referring to the data base I have, not my query!!!!

[JAGster]

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

[NeoPa]

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.

[stephenx]

Such an elegantly simple fix - thanks JAGster!