Wildcard Join???

[mindbender]

I have 2 tables. Both Have a 9 digit Territory field.

Table 1 contains all territory data examples aaaaaa001, aaaaaa002, aaaaaa003
Table 2 contains only territories covered aaaaaa001

In reality as long as the first 6 digits match, it would be considered a covered territory. New territories get added to Table 1 all the time but Table 2 is stagnant, so if a new territory is added to table 1 and should be covered it is never picked up by table 2.



I am wondering if there is a way to search Left([Table2].[Territory] = Left([Table1].[Territory] so that anything aaaaaa gets pulled back from table 1.

I hope this is making sense.

My thought is add a 6 digit field to each table - query on those fields joined and return the 9 digit in the result. But I am not sure if there is an easier or more standard way or if doing this will cause my calculations to get messed up by now have a column with duplicate values.

Any advice would be appreciated

Thanks

[isladogs]

You don't need to create new fields
Create a Cartesian query with both tables but no link between them
Add the Territory field from both tables
Now add a Table2 field Left(Territory,6) and in the criteria for that field enter Left(Table1.Territory,6) so it only picks up records where these are identical
Run the query

[mindbender]

You don't need to create new fields
Create a Cartesian query with both tables but no link between them
Add the Territory field from both tables
Now add a Table2 field Left(Territory,6) and in the criteria for that field enter Left(Table1.Territory,6) so it only picks up records where these are identical
Run the query

That is cool. Definitely pointing me in the right direction.
Thank you so much

[CJ_London]

you can also modify the join in sql, but will not then be editable in the query window, but should be faster (subject to indexing) than a Cartesian query

SELECT *
FROM Table1 INNER JOIN Table2 ON Left([Table2].[Territory],6) = Left([Table1].[Territory],6)