Query Criteria to look at first 50 characters only in matching fields

[matratus34]

Hi,

I’m linking 2 tables and want to bring back results where fields in my 2 tables match.
My issue is that the fields only match for a maximum of 50 characters so if the field is greater than 50 characters the query won’t see them as a match and won't return anything.

I want to return any fields that match for the 1^st 50 characters.

Below is an Example – I’ve used 5 characters in the example to make it easier to see

[TABLE="width: 293"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD]Table 2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Description Field[/TD]
[TD] [/TD]
[TD]Description Field[/TD]
[/TR]
[TR]
[TD]Name123[/TD]
[TD] [/TD]
[TD]Name1xxxx[/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD] [/TD]
[TD]Matthew[/TD]
[/TR]
[TR]
[TD]Tree1xx[/TD]
[TD] [/TD]
[TD]Treex125[/TD]
[/TR]
</tbody>[/TABLE]

On this example the descriptions in the tables match for the 1st 5 characters so I'd want to return them as if they were the same.

Can anyone help with an expression that would do this?

Hope this makes sense!

Thanks in advance as always

 

[Joe4]

Hope this makes sense!

I am afraid it does not, at least not to me.
You seem to have conflicting requirements.

I want to return any fields that match for the 1^st 50 characters.

On this example the descriptions in the tables match for the 1st 5 characters so I'd want to return them as if they were the same.

Are you wanting to match on the first 50 characters or the first 5?
If for the first 50, only "Matthew" would be a match in your three examples.

 

[matratus34]

My real life issue is 50 characters - but for my example rather than type out field names of 50+ characters I thought it might be easier to show the same problem but based on the 1st 5 characters.
Probably made it more confusing?

 

[Joe4]

My real life issue is 50 characters - but for my example rather than type out field names of 50+ characters I thought it might be easier to show the same problem but based on the 1st 5 characters.
Probably made it more confusing?

OK, I gotcha now.

There are a few ways to do this:

1. Create a query off of each table, and add a calculated field that takes the first 50 characters of your matching field (using the LEFT function). Then, create a new query using this two queries, joining them on the new calculated fields in each query.

- or -

2. Create a query between the two tables with no join. Then, in the WHERE clause of the query, enter:

LEFT([Table1].[Description],50) = LEFT([Table2].[Description],50)

Either one should work. I usually prefer the first method, as I am not a huge fan of queries between tables with no joins. But that is just my own personal preference.

 

[james_lankford]

I just did a test and did this as a join and it seemed to work (which surprised me -- I thought for sure it would fail in MS Access)
of course I had to do it in SQL View, not design view

2. Create a query between the two tables with no join. Then, in the WHERE clause of the query, enter:
Code:
LEFT([Table1].[Description],50) = LEFT([Table2].[Description],50)

 

[Joe4]

I just did a test and did this as a join and it seemed to work (which surprised me -- I thought for sure it would fail in MS Access)
of course I had to do it in SQL View, not design view

You can actually do it in Design View (without using SQL View).
Just create the query, adding the two tables.
Then, add the calculated field for Table1, uncheck the "Show" button, and add this to the criteria of that field:

=Left([Table2]![Description],5)

That does the same thing without requiring your to swtich to SQL View and write it manually.

 

[matratus34]

Hi Joe4 - thanks for taking the time to help me, it's greatly appropriated.

I've not had chance to try it out yet but will do first thing in the morning - thank you!

 

[Joe4]

You are welcome.