Join based on part of a field

[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!

 

[CorporateQAinTX]

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.

 

[Duane Hookom]

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.

 

[PK]

Duane, Thank you very much - works like a champ!!!!!

CorporateQAinTX - thank you also!!!!

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

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!

 

[KARL DEWEY]

Using 'Like' will connect "Project1" with "Project123".
--
KARL DEWEY
Build a little - Test a little

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 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!