creating join with only the first 4 characters of a field

  • Thread starter Thread starter himmelrich
  • Start date Start date
the best way to join on just 4 characters is to do it in the SQL view.

If you want to do it in the query design view (the grid). You can build a
query on the first table with the calculated field

Field: JoinThis: Left(SomeField,4)

Save that query
Now build a query on the second table doing the same thing

Now build a query with the two saved queries as the sources and join on the
two calculated fields.

Alternative
Build the query on the two tables and join on the fields that you need.
Switch to SQL view and find the clause

TableA INNER JOIN TableB ON TableA.Somefield =TableB.AnotherField

Edit that phrase to
TableA INNER JOIN TableB ON Left(TableA.Somefield,4) =
Left(TableB.AnotherField,4)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top