Relate based on part of a field(like)

[peciam]

Hi,

Can you create a relationship from one table to another based on only part of the field?

Example = Table 1, Field name = "Berlin TA"
Table 2, Local Name = "Berlin Teachers"

I want to link based on "Berlin"

Thanks,

TC

 

[dhookom]

In table 1, is the field name actually "Berlin TA"? How about providing some actual table and field names with sample data?

Is the text you want to join on always the first characters to the left of the first space?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[peciam]

Hi Duane,

Heres what I have, of course it's a favor for someone, not even my work.

Two tables,
Tabel 1 = Tab_Local
Table 2 = Tab_Match

I need to update the Tab_match field called Local Number. Currently it is blank.

Table 1 has the field = Local_Name_1
Table 2 has the field = Local_name_2

Data in Tabel 1, Local_Name_1 is not exactly the same as Table 2, Local_name_2. It is not an exact match. Only part of the complete name.

[Table_1].[local_name_1] ="Berlin TA"
[Table_2].[local_name_2] ="Berlin Teachers"

I Want to update the local number from table_1 to local number if the two tables either match or have a partical match.

I'm trying to be clear, and I appreciate any help you can provide.

Thanks,

TC

 

[dhookom]

How would you handle
[Table_1].[local_name_1] ="Berlin Teachers"
[Table_2].[local_name_2] ="Munich Teachers"

This would also be a partial match but would probably not be desired.

Have you thought through how you would create an expression for acceptable and unacceptable matches?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[peciam]

Hey Duane,

Great thought! No I haven't thought it through.

Should I parse out the field? Like pull out Berlin into a seperate field and then do the match?

TC

 

[dhookom]

I'm not sure what/how you should accomplish this to find unique values to test your values against. You've provided only a couple values that don't create any kind of pattern.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]