Join based on part of a field

  • Thread starter Thread starter PK
  • Start date Start date
P

PK

I need to match up records in two tables based on PART of a field, which
could be variable in length. For instance:

Table1 has a field [Project ID] with data "Project1", "Project123", etc...

Table2 has a field [OtherPrjID] with data "Project1-fred", "Project1-Sally",
"Project123-nancy", "Project123-henry"

I need to match:
Table1 Record "Project1" with Table2 "Project1-fred" and "Project1-Sally"
records
and
Table1 Record "Project123" with Table2 "Project123-nancy" and
"Project123-henry" records

Please Note: It is possible that there will not be any matches in Table2

Thank you!
 
Not that I'm an expert, but I would use a wildcard. Something like

WHERE ((Table2.OtherPrjID) Like [Table1]![ProjectID]*);

I think that would be the right syntax, but I would probably have to tinker
with it to get it just right.

If there is no match, it just wouldn't return a result.

Hope this helps.
 
I would not have stored 2 pieces of data "Project1" and "Sally" in the same
field. However, you can create a query that parses the two values into two
columns:

SELECT *, Left(OtherPrjID, Instr(OtherPrjID,"-")-1) as OtherProject,
Mid(OtherPrjID, Instr(OtherPrjID,"-")+1) As Person
FROM Table2

Then use this query to LEFT or RIGHT JOIN to Table1.
 
Duane, Thank you very much - works like a champ!!!!!

CorporateQAinTX - thank you also!!!!



Duane Hookom said:
I would not have stored 2 pieces of data "Project1" and "Sally" in the same
field. However, you can create a query that parses the two values into two
columns:

SELECT *, Left(OtherPrjID, Instr(OtherPrjID,"-")-1) as OtherProject,
Mid(OtherPrjID, Instr(OtherPrjID,"-")+1) As Person
FROM Table2

Then use this query to LEFT or RIGHT JOIN to Table1.

--
Duane Hookom
Microsoft Access MVP


PK said:
I need to match up records in two tables based on PART of a field, which
could be variable in length. For instance:

Table1 has a field [Project ID] with data "Project1", "Project123", etc...

Table2 has a field [OtherPrjID] with data "Project1-fred", "Project1-Sally",
"Project123-nancy", "Project123-henry"

I need to match:
Table1 Record "Project1" with Table2 "Project1-fred" and "Project1-Sally"
records
and
Table1 Record "Project123" with Table2 "Project123-nancy" and
"Project123-henry" records

Please Note: It is possible that there will not be any matches in Table2

Thank you!
Click to expand...
Click to expand...
 
Using 'Like' will connect "Project1" with "Project123".
--
KARL DEWEY
Build a little - Test a little


CorporateQAinTX said:
Not that I'm an expert, but I would use a wildcard. Something like

WHERE ((Table2.OtherPrjID) Like [Table1]![ProjectID]*);

I think that would be the right syntax, but I would probably have to tinker
with it to get it just right.

If there is no match, it just wouldn't return a result.

Hope this helps.

PK said:
I need to match up records in two tables based on PART of a field, which
could be variable in length. For instance:

Table1 has a field [Project ID] with data "Project1", "Project123", etc...

Table2 has a field [OtherPrjID] with data "Project1-fred", "Project1-Sally",
"Project123-nancy", "Project123-henry"

I need to match:
Table1 Record "Project1" with Table2 "Project1-fred" and "Project1-Sally"
records
and
Table1 Record "Project123" with Table2 "Project123-nancy" and
"Project123-henry" records

Please Note: It is possible that there will not be any matches in Table2

Thank you!
Click to expand...
Click to expand...
 
Back
Top